INNER JOIN, LEFT JOIN, RIGHT JOIN??? HELP!

[schone]

Hi

Just a summary about my tables:

[b]players_answers[/b]
- id (auto_inc)
- date
- player_id
- value

[b]players[/b]
- id (auto_inc)
- player_id
- givenname
- surname

Here is my query:

[code]
SELECT players_answers.player_id, players_answers.date, players.player_id, players.givenname,  players.surname
FROM players_answers
INNER JOIN players
ON (players_answers.player_id = players.player_id AND date=NOW())
GROUP BY players_answers.player_id
[/code]

What i'm trying to do is grab all the players names that have an entry on today's date. [!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]Which works fine![!--colorc--][/span][!--/colorc--]

[b]My problem is[/b] that how would I go about getting all the players name's that don't have an entry on this date. So the exact opposite of this query!

I have tried to use date!=NOW(). But the problem is all players in the players_answers have dates ranging back to Janurary, so all players get displayed when I put that clause in.

[b]Please someone help![/b]

Thank You!

[fenway]

Try the following (UNTESTED):

[code]SELECT players_answers.player_id, players_answers.date, players.player_id, players.givenname,  players.surname
FROM players
LEFT JOIN players_answers ON (players_answers.player_id = players.player_id AND date=NOW())
WHERE players_answers.player_id IS NULL[/code]

BTW, you shouldn't have the group by in either of these.

[schone]

[!--quoteo(post=372954:date=May 10 2006, 10:59 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 10 2006, 10:59 AM) [snapback]372954[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Try the following (UNTESTED):

[code]SELECT players_answers.player_id, players_answers.date, players.player_id, players.givenname,  players.surname
FROM players
LEFT JOIN players_answers ON (players_answers.player_id = players.player_id AND date=NOW())
WHERE players_answers.player_id IS NULL[/code]

BTW, you shouldn't have the group by in either of these.
[/quote]


Hi Fenway!

The following query displays every player_id. :(

I have asked every person I know that knows mySql about this and no one can work it out!

[shoz]

[quote]
[quote]

Try the following (UNTESTED):

[code]
SELECT players_answers.player_id, players_answers.date, players.player_id, players.givenname,  players.surname
FROM players
LEFT JOIN players_answers ON (players_answers.player_id = players.player_id AND date=NOW())
WHERE players_answers.player_id IS NULL
[/code]

BTW, you shouldn't have the group by in either of these.
[/quote]


Hi Fenway!

The following query displays every player_id. :(

I have asked every person I know that knows mySql about this and no one can work it out!
[/quote]
From the way you've described your tables, as far as I can see fenway's query should have worked. I have tested it on sample data and get the desired results. Perhaps you should post the script you're currently using to retrieve the players. You may have forgotten something.


The following should also work, but I'd assume the JOIN would be faster.
[code]
SELECT
player_id, givenname, surname
FROM
players
WHERE player_id
NOT IN
    (
    SELECT
    players.player_id
    FROM players_answers
    INNER JOIN players
    ON (players_answers.player_id = players.player_id AND date=NOW())
    )
[/code]