embedded select / left join question

[Jim Miller]

This is probably a real newbie question, but, since that's what I am...

Using MySQL 3.23, I have two tables, for instance:

        ContestEntries: id, entrant, contestNumber, etc.
        Entrant: id, name, etc.
There are multiple contests, and Entrants can enter as many as they 
like.  So, Entrant might look like:

        id=1, name=John
        id=2, name=Mary
        id=3, name=Bill
and ContestEntries might look like:

        id=1, entrant=1, contestNumber=1
        id=2, entrant=2, contestNumber=2
        id=3, entrant=1, contestNumber=2
I want to do a join and select of these tables that will give me all 
the Entrants who did NOT enter contest 1 (i.e., Mary and Bill).

I've been told that the "right" way to do this is with an embedded 
select, which MySQL 3.23 doesn't have.  So, I've been trying to do it 
with a left join.  The closest I've been able to come is something like:

        select entrant.name from entrant
        left join contestEntries
        on entrant.id = contestEntries.entrant
        where contestEntries.entrant is null
                or contestEntries.contestNumber != 1
This obviously doesn't work -- it finds Bill, since he didn't enter 
either contest, and it finds Mary, since there is no row in 
contestEntries for her where contestNumber = 1.  But it also finds 
John, because of contestEntries id=3 -- its contestNumber is not equal 
to 1, and so the WHERE clause is satisfied.

Does anyone have any suggestions for a way to do this?  Again, I'm 
looking for a 3.23-compatible solution....

Thanks,
Jim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]