mysql enigma

[Guest]

Can someone please give me a push into the right direction to find the query I need for MySQL?
I've ben strugling with it all night with no success..

I have two tables:

Table P (Persons)
NR NAME
1 Bill
2 Eva
3 John
4 Ben
5 Susan

Table M (Meetings)
HOSTNR GUESTNR
5 1
3 1
3 5
5 3

(hostnr and guestnr are the numbers corresponding with NR in the table P)

Now I need a list of all Persons NAME that aren't guests of 3.
In this example they are 2 and 4 (since 3 cannot be it's own guest as well).

It should be something like:
SELECT P.NAME FROM P WHERE (P.NR NOT IN (SELECT M.GUESTNR FROM M WHERE M.HOSTNR=3)) AND P.NR <> 3

But I found out that MySQL doesn't cope with subqueries.
Is there a solution with some kind of JOIN construction?

TIA/Jan

[Bill Karwin]

Re: mysql enigma

someone@somedom ain.com.invalid wrote:[color=blue]
> It should be something like:
> SELECT P.NAME FROM P WHERE (P.NR NOT IN (SELECT M.GUESTNR FROM M WHERE M.HOSTNR=3)) AND P.NR <> 3
>
> But I found out that MySQL doesn't cope with subqueries.[/color]

FWIW, MySQL 4.1 supports subqueries.
[color=blue]
> Is there a solution with some kind of JOIN construction?[/color]

SELECT P.NAME FROM P LEFT OUTER JOIN M
ON (P.NR = M.GUESTNR AND M.HOSTNR = 3)
WHERE P.NR <> 3 AND M.HOSTNR IS NULL

Regards,
Bill K.