[Resolved] Selected two table fields...

Sharif

First, this will be used in a page where I can review games and stuff. The review table is setup like this:

|id|gameid|review|

The games table:
|id|title|info|

Now in the form of the review, I'd like to pick the title of every game in the games table that has not been reviewed, meaning that in the reviews table, there is no record that has the gameid. I know it's confusing, that's why I can't figure it out. How can I do a query for this?

sid

what you want is to intersect 2 queries

select all games
aelect all games with reviews

mysql only supports this with one table type (i wish i could think of which one check the mysql docu!) - otherwise you have to write a workaround with some php statements.... it really isnt too hard.

LordShryku

Actually, sounds like a simple LEFT JOIN....

   SELECT g.*
     FROM games g
LEFT JOIN review r
       ON g.id = r.gameid
    WHERE r.gameid IS NULL

Sharif

Ok here is an update:

the games table:
id|title

the reviews table:
id|gid|review

What I am trying to do is select all games that have not been reviewed, meaning that in the reviews table there will be no entries where the gid is available. Obviously if there is no entry in the reviews table with a gid, that game has not been reviewed. I am trying to retrieve those.

So I took Lord's query and adjusted it and here it is:

//Get games
$sqlREV = "SELECT ";
$sqlREV.= "g.id, g.title ";
$sqlREV.= "FROM ";
$sqlREV.= "games g ";
$sqlREV.= "LEFT JOIN ";
$sqlREV.= "review r ";
$sqlREV.= "ON ";
$sqlREV.= "g.id = r.gid ";
$sqlREV.= "WHERE ";
$sqlREV.= "r.gid IS NULL ";
$sqlREV.= "ORDER BY ";
$sqlREV.= "g.title ";
$sqlREV.= "ASC";
$queryREV = mysql_query($sqlREV);

This returns no results at all 🙁

LordShryku

$sqlREV.= "review r ";

Is it reviews or review?

Sharif

Heh, oops! It was reviews

Problem Solved!

sid

Originally posted by LordShryku
Actually, sounds like a simple LEFT JOIN....
   SELECT g.*
     FROM games g
LEFT JOIN review r
       ON g.id = r.gameid
    WHERE r.gameid IS NULL
[/B]

sucker - you totally took advantage of me not having slept for 36 hours!

LordShryku

I see a man down....I start kicking 😃