Finding Records Where a JOIN doesnt exist

[jh_dempsey]

Hey All

 

At the moment ive got two tables. One holds all the entrants details like their competitor number (bib number), name, and the events they have entered (k1 and c1) The other holds the competitors score for the event.

 

nsr_entrants:

 

| id | bibnumber | name | k1| c1 |

 

 

k1_slalom_scores:

 

| entrant_id | score |

 

 

I want to be able to find out if there are any competitors who have been assigned a bib number (ie their bib number is NOT zero), are taking part in the K1 event, and have not been given a score (ie. their entrant_id doesnt exist in the k1_slalom_scores table). If i find any, then i want to output their names to a table.

 

Looking through the mySQL manual i found this which will do roughly what i want:

 

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table: 

mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL. See Section 7.2.8, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN”.

 

Just to keep things simple to start with, i just tried to get the simple join going. I would try and get the other parameters (like being entered in K1 and bib number not equal to zero) added on later.

 

I tried modifying the above to my table like below but had no luck

 

SELECT *.nsr_entrants FROM nsr_entrants LEFT JOIN k1_slalom_scores ON nsr_entrants.id = k1_slalom_scores.entrant_id WHERE k1_slalom_scores.entrant_id IS NULL

 

Can anyony help me out and tell me what im doing wrong??

 

 

 

 

[ryanlwh]

[!--fonto:Courier New--][span style=\"font-family:Courier New\"][!--/fonto--]*.nsr_entrants[!--fontc--][/span][!--/fontc--]? did you mean [!--fonto:Courier New--][span style=\"font-family:Courier New\"][!--/fonto--]nsr_entrants.*[!--fontc--][/span][!--/fontc--]?

[jh_dempsey]

D'oh!!!

 

Just realised that what i did was right, i just had a mistype when including the string that held my connection details. Now it all works fine...