Complex SQL Join Statement Sorta Works...Need Help!

[socalnate]

Here's the statement:

 

SELECT gamesnew.game_name, sort_date, thumb, flash_file, genres.genre, descriptions.description, ratings.num_votes, score

FROM gamesnew

LEFT JOIN genres ON gamesnew.game_name = genres.game_name

JOIN descriptions ON genres.game_name = descriptions.game_name

JOIN ratings ON descriptions.game_name = ratings.game_name

 

Each table has one column [game_name] that is unique per table (ie the game names aren't duplicated).  I set the KEY as the ID (which varies per table so it's useless for my purposes) but my game_name in each table I set to UNIQUE so that for each table the unique identifier is the game name and nothing else.

 

The SQL statement works but the problem is is that it doesn't select ALL the [game_name]'s from the table gamesnew even if the info isn't complete for the other tables.  It should (in theory) select all 18,000 game_name and display them with the joined content from the other tables and just leave blanks in the incomplete info right?  I don't know why it's only selecting about 1,500 games (but oddly all the info is complete with the data it does select).     

 

???

[bubblegum.anarchy]

All the joins need to be LEFT JOINS

[socalnate]

Thanks, your suggestion worked (partially).   Here's the SQL statement I used:

 

 

SELECT gamesnew.game_name, sort_date, thumb, flash_file, genres.genre, descriptions.description, ratings.num_votes, score

FROM gamesnew

LEFT JOIN genres ON gamesnew.game_name = genres.game_name

LEFT JOIN descriptions ON genres.game_name = descriptions.game_name

LEFT JOIN ratings ON descriptions.game_name = ratings.game_name

 

Now I have the problem where in my ratings table it isn't selecting ALL of the columns [num_votes] or [score] even though in those columns where the data is missing the game_name is exactly the same.  How come the join is partially working?  It's weird, I did some sampling of the game_name to make sure they were exactly the same and they were.  Any ideas? 

 

???

[rcorlew]

left joins only returns relevant data(only table.rows) that are not null

 

right joins return all data, whether it is null or not.

 

If you want to return rows that have null rows, you should try using right join

[fenway]

left joins only returns relevant data(only table.rows) that are not null

 

right joins return all data, whether it is null or not.

 

If you want to return rows that have null rows, you should try using right join

No... never use right join, just flip the tables around.

[rcorlew]

Then they would need to an OUTER JOIN, INNER JOINS will only return non-empty rows, it appears that they would like to select all rows based on the post even if data is missing from any of the rows for num_votes and scores.

[fenway]

Yes, and a LEFT JOIN is an outer join that's standard.