SQL question (not JDBC)

Hey!

Hope someone can help me with a little SQL problem that I have..

Two tables... A and B. A has one field called nick which also exists in B. B also has another field (status) which is an enum. Not every nick that exists in A exists in B.

I want to extract all nicks from A WITHOUT the ones that exist in B where the value of the enum is (say) STATUS_OK. So if they dont exist in B I want them.

I have tried with:

SELECT *
FROM A
LEFT OUTER JOIN B USING (nick)
WHERE user_sites.nickname != null OR user_sites.status = 'STATUS_OK'

but it doesnt work. What should I do?

Thanks! //Tom

Try this
Select * from A
where nick not in (Select nick from B where status = 'STATUS_OK')

I'm not sure how efficient this is going to be though

I get a syntax error... Can you write like that using MySQL?

Try this.

SELECT *
FROM A
LEFT JOIN B ON A.nick = B.nick
WHERE B.status = 'STATUS_OK'

Originally posted by venkatraman kandaswamy:
Try this.

SELECT *
FROM A
LEFT JOIN B ON A.nick = B.nick
WHERE B.status = 'STATUS_OK'

But that will give him all the records in A that are also in B. He wants records in A that are NOT in B

Hi Jayesh nice to meet you again

then will this work? It will be nice if we can see some records of the 2 tables.

SELECT *
FROM A
LEFT JOIN B ON A.nick = B.nick
WHERE B.status <> 'STATUS_OK'

Hey there Venkat

I think that one is closer but what about records in A that dont have any records in B?

I don't get those...... How do I do that?

Records of the two tables....

A:

nick:
tom
pete
carl
joe
clara

B:
pete STATUS_OK
nicholas STATUS_BAD
carl STATUS_BAD

Should generate:
tom
carl
joe
clara

Hope this helps.

I have solved it now.... I had = NULL, should have been IS NULL.

SELECT *
FROM A
LEFT JOIN B USING nick
WHERE
B.nick IS NULL OR
B.status <> 'STATUS_OK'

Such a small error.... :-) Thanks for your input!