[SOLVED] Displaying rows of tables where ID does not exist in other table.

[Siggles]

In my Dbase we have 2 tables - Predictions and Fixtures. They are linked by colum 'id'.

 

I am able to show all fixtures where the user has predicted but I want to show all fixtures where the user hasn't. To show where they all HAVE (and this works)...

 

SELECT fixtures.dateplayed, fixtures.opponent, fixtures.resultus, fixtures.resultthem, predictions.resultmfc, predictions.resultother, predictions.score FROM predictions Right Join fixtures ON fixtures.id = predictions.id AND predictions.username = '$session->username' ORDER BY fixtures.dateplayed DESC

 

So I tried...

 

SELECT fixtures.dateplayed, fixtures.opponent FROM predictions Right Join fixtures ON fixtures.id != predictions.id AND predictions.username = '$session->username' ORDER BY fixtures.dateplayed DESC

and that didn't work. It just showed the prdedictions over about 10 times.

 

I have tried IS NULL but if a prediction has not been made for a fixture then a row doesn't exist at all in predictions table. Is there a 'does not exist at all' SQL command. If you need mroe info let me know.

 

Thanks

[rhodesa]

Try

 

SELECT dateplayed, opponent FROM fixtures WHERE id NOT IN (SELECT id FROM predictions) AND predictions.username = '$session->username' ORDER BY dateplayed DESC

[Siggles]

Thank you for your help. I sort of see what you are getting at, however SQL errors...

 

MySQL said: 

 

#1109 - Unknown table 'predictions' in where clause

 

There is definitely a predictions table? It doesn't like the bit in brackets though.

[rhodesa]

Oops...try this:

 

SELECT dateplayed, opponent FROM fixtures WHERE id NOT IN (SELECT id FROM predictions WHERE username = '$session->username') ORDER BY dateplayed DESC

[Siggles]

Oops...try this:

 

SELECT dateplayed, opponent FROM fixtures WHERE id NOT IN (SELECT id FROM predictions WHERE username = '$session->username') ORDER BY dateplayed DESC

 

You got it! Thank you very much! :-D

[Siggles]

Hi, Now I want to show all fixtures where the user has made a prediction. I tried changing NOT IN for IN but that doesn't work. Any ideas?

[rhodesa]

I am able to show all fixtures where the user has predicted but I want to show all fixtures where the user hasn't. To show where they all HAVE (and this works)...

 

SELECT fixtures.dateplayed, fixtures.opponent, fixtures.resultus, fixtures.resultthem, predictions.resultmfc, predictions.resultother, predictions.score FROM predictions Right Join fixtures ON fixtures.id = predictions.id AND predictions.username = '$session->username' ORDER BY fixtures.dateplayed DESC

 

You said you had that working already? You JOIN here looks good.

[Siggles]

Sorry, worked it out.

 

Change NOT IN for IN but make < to >

 

Derr!

 

:-)

[Siggles]

In fact no, just change NOT IN to IN. It was just that there were no fixtures without scores that were later than today's date.