TotalWhat Posted October 4, 2014 Share Posted October 4, 2014 (edited) This question has been solved. The solution is at the bottom. Hi! So here's the question for you. I use Microsoft SQL Server Management Studio. I have a table 'Frogs', that has two attributes. FrogName and FrogHabitat. If I do a select * from the table the output is something like this: FrogName FrogHabitat Wood frog Iceland Wood frog Denmark Wood frog Sweden Sky frog Iceland Sky frog Germany Hill frog France Hill frog Sweden The question is: "Write the query that would produce the FrogName of pairs of Frogs whom both share the same FrogHabitat". And the final output should look like this: FrogName FrogName Sky frog Wood frog Wood frog Sky frog Hill frog Wood frog Wood frog Hill frog I have thought about this allot but I can't figure out what the right query would be. Any help along the way to enligthment would be much appreciated! Thank you! Edited October 6, 2014 by TotalWhat Link to comment Share on other sites More sharing options...
Ingolme Posted October 4, 2014 Share Posted October 4, 2014 You can ORDER BY habitat. Frogs of the same habitat will be grouped together. When looping through the results in PHP you can check when the habitat name changes in order to put separations between them. Link to comment Share on other sites More sharing options...
TotalWhat Posted October 4, 2014 Author Share Posted October 4, 2014 You can ORDER BY habitat. Frogs of the same habitat will be grouped together. When looping through the results in PHP you can check when the habitat name changes in order to put separations between them. I don't use PHP, just mssql. I didn't really understand. I have tried to use order by to achieve my results but failed so far. Could you please explain in more detail. Thank you! Link to comment Share on other sites More sharing options...
Ingolme Posted October 4, 2014 Share Posted October 4, 2014 I'm not exactly sure how you would get SQL to show two results of the same field twice in each row. Are you sure you understood the question properly? By using the ORDER BY property, frogs of the same habitat will be grouped together. If you use ths query SELECT * FROM Frogs ORDER BY FrogHabitat the results will be like this: FrogName FrogHabitat Wood frog Denmark Hill frog France Sky frog Germany Wood frog Iceland Sky frog Iceland Hill frog Sweden Wood frog Sweden Link to comment Share on other sites More sharing options...
davej Posted October 5, 2014 Share Posted October 5, 2014 I would think you might want to go look up the topics "self join" and/or "sub query." Link to comment Share on other sites More sharing options...
TotalWhat Posted October 6, 2014 Author Share Posted October 6, 2014 I managed to solve the question with the help of the suggested self-join. select t1.FrogName, t2.FrogName from Frog t1, Frog t2 where t1.FrogName != t2.FrogName and t1.FrogHabitat = t2.FrogHabitat; Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now