Jump to content


Photo

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


  • Please log in to reply
3 replies to this topic

#1 schone

schone
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 10 May 2006 - 12:21 PM

Hi

Just a summary about my tables:

players_answers
- id (auto_inc)
- date
- player_id
- value

players
- id (auto_inc)
- player_id
- givenname
- surname

Here is my query:

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

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--]

My problem is 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.

Please someone help!

Thank You!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 May 2006 - 03:59 PM

Try the following (UNTESTED):

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

BTW, you shouldn't have the group by in either of these.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 schone

schone
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 13 May 2006 - 06:05 AM

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

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

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!



#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 May 2006 - 04:33 PM


Try the following (UNTESTED):

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

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



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!

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users