Jump to content

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


socalnate

Recommended Posts

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).     

 

???

Link to comment
Share on other sites

Thanks, your suggestion worked (partially). :D  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? 

 

???

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.