SQL query to intersect

Question

I'm having a lot of issues with SQL, I'm not sure how to do what I need, but the series of steps is this:

Have two tables. Table 1 has a 'game_id'

Table 2 has a 'game_id' and 'id' Where 'game_id' is the same as the first table.

Table 1 is a list of games. Table 2 is a list of franchises.

I'm trying to make a sql query that will ONLY show the games from a franchise.

I have this:

SELECT * FROM gamedata_similar_games
INNER JOIN gamedata_franchises
ON gamedata_franchises.id='244'
WHERE gamedata_similar_games.game_id= '6959'

But it returns 335 results. The game is in a franchise with 6 games in it.

Is this too unclear?

Try researching about the purpose of joins. That will help you a lot, not only with this particular query. — usr, Jun 15 '12 at 22:01

score 0 · Accepted Answer · answered Jun 15 '12 at 22:00

0

You have to put the related fields on the ON clause

SELECT * FROM gamedata_similar_games G
INNER JOIN gamedata_franchises F
ON G.game_id = F.game_id 
WHERE F.id= '244'

answered Jun 15 '12 at 22:00

crassr3cords

This particular query doesn't seem to yield results I'm expecting. It returns 203 I only expect 6 rows. – Adola Jun 15 '12 at 22:04
Have yo tried running separater querys first ? I mean, SELECT * FROM gamedata_franchises WHERE id = '244', how many records are ?... and SELECT * FROM gamedata_similar_games WHERE game_id IN (SELECT game_id FROM gamedata_franchises WHERE id = '244') ? – crassr3cords Jun 15 '12 at 22:08
SELECT * FROM gamedata_similar_games WHERE game_id IN (SELECT game_id FROM gamedata_franchises WHERE id = '244') Yields 6 results. SELECT * FROM gamedata_similar_games WHERE game_id IN (SELECT game_id FROM gamedata_franchises WHERE id = '244') = 6959 Yields nothing. I'm just not sure what I'm doing wrong here. – Adola Jun 15 '12 at 22:42
Why do you say tha still no go ? – crassr3cords Jun 15 '12 at 22:44
But I think I see why it should be working, but can't understand why I'm not seeing the right results. – Adola Jun 15 '12 at 22:49
First, how many records the query SELECT * FROM gamedata_franchises WHERE id = '244', then if it's true what you say, run this SELECT * FROM gamedata_similar_games WHERE game_id IN (EACH ID RETURNED IN YOUR FIRST QUERY) – crassr3cords Jun 15 '12 at 22:49

1 Answers1