Complex Join Statement

[jh_dempsey]

Hi All

 

My database is set up with 3 tables with the following column names and a samle set of data

 

nsr_entrants

 

|entrant_id | Name |

--------------------

|      1      |  Jon  |

|      2      |  Ken  |

|      3      |  Bob  |

 

 

nsr_bibno:

 

|entrant_id | bibno |

--------------------

|    1        |  100  |

|    2        |  200  |

 

 

nsr_scores:

 

| entrant_id | score |

---------------------

|      1      |  20  |

|      3      |  20  |

 

 

 

What i need to do is find the entrants (listed within the nsr_entrants table) who have a bib number registered in the nsr_bibno table, but who dont have a score registered in the nsr_scores table.

 

So in the above example, i would want the statement to pick out Ken (id=2) because that person has a bib number registered, but has no score registered.

 

I know how to join the tables seperatley, but im not sure how to do both in one go. My attempts so far have failed.

 

Im using mySQL version 5.0.45

 

Anyone able to help me out?

[toplay]

I would imagine something like this:

 

SELECT

            ne.*

  FROM

            nsr_entrants ne

    JOIN

            nsr_bibno nb

      ON

            nb.entrant_id = ne.entrant_id

LEFT JOIN

            nsr_scores ns

      ON

            ns.entrant_id = ne.entrant_id

WHERE

            ns.entrant_id IS NULL

;

 

Based on your sample data, the above query should show only Ken (# 2).