Darkmatter5 Posted February 10, 2009 Share Posted February 10, 2009 Here are my tables game_collections gs_idmember_id 10122 2022 222 1622 game_systems gs_idgame_idsystem_id 256 16816 2016 10146 games game_idtitle 1Fable 2 4Fallout 3 5Blue Dragon 8The Legend of Zelda: Twilight Princess systems system_idname 6Xbox 360 16Wii Now I'm wanting to get a list of games on a specific system that a specific member has in their collection. So I'm trying to build a query to do this across all 4 tables. PLEASE help! Here's what I have so far. SELECT g.title, s.name AS sysname FROM game_collections gc JOIN game_systems USING(gs_id) WHERE gc.member_id=22 AND s.system_id=6 ORDER BY g.title ASC Quote Link to comment Share on other sites More sharing options...
premiso Posted February 10, 2009 Share Posted February 10, 2009 SELECT g.title, s.name AS sysname FROM games g, game_collections gc, game_systems gs, systems s WHERE g.game_id = gs.game_id AND gs.system_id = s.system_id AND gc.gs_id = gs.gs_id AND gc.member_id=22 AND s.system_id=6 ORDER BY g.title ASC See if that works. Quote Link to comment Share on other sites More sharing options...
Darkmatter5 Posted February 11, 2009 Author Share Posted February 11, 2009 Worked great! Quick question though. What are good practices for when and when not to use joining in a query versus just where x=x? Quote Link to comment Share on other sites More sharing options...
premiso Posted February 11, 2009 Share Posted February 11, 2009 Worked great! Quick question though. What are good practices for when and when not to use joining in a query versus just where x=x? I never really understood the JOINS. I guess I should read up on it. Found this tid bit which may help you: Different SQL JOINs Before we continue with examples, we will list the types of JOIN you can use, and the differences between them. * JOIN: Return rows when there is at least one match in both tables * LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table * RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table * FULL JOIN: Return rows when there is a match in one of the tables From : http://www.w3schools.com/Sql/sql_join.asp Also found this in Wiki http://en.wikipedia.org/wiki/Join_(SQL) I am going to do some reading now EDIT: The join in the code I gave you is considered an "INNER JOIN" as an fyi Quote Link to comment Share on other sites More sharing options...
fenway Posted February 15, 2009 Share Posted February 15, 2009 There are plenty of excellent resources in the stickies... you don't have to google for them. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.