wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL question (not JDBC) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL question (not JDBC)" Watch "SQL question (not JDBC)" New topic
Author

SQL question (not JDBC)

Tomas Nilson
Ranch Hand

Joined: Jan 14, 2002
Posts: 33
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
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
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
Tomas Nilson
Ranch Hand

Joined: Jan 14, 2002
Posts: 33
I get a syntax error... Can you write like that using MySQL?
Venkatraman Kandaswamy
Ranch Hand

Joined: Jul 07, 2004
Posts: 120
Try this.

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


--Venkatraman<br />SCJP 1.4<br /><a href="http://kvrlogs.blogspot.com" target="_blank" rel="nofollow">blog</a>
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
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
Venkatraman Kandaswamy
Ranch Hand

Joined: Jul 07, 2004
Posts: 120
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'
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
Hey there Venkat

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

Joined: Jan 14, 2002
Posts: 33
I don't get those...... How do I do that?
Tomas Nilson
Ranch Hand

Joined: Jan 14, 2002
Posts: 33
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.
Tomas Nilson
Ranch Hand

Joined: Jan 14, 2002
Posts: 33
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!
 
 
subject: SQL question (not JDBC)
 
Similar Threads
Serialize/Deserialize Java enum
Regarding the SQL querry generation
(URLyBird) 1.3.1 reusage of deleted entries
SQL injection?
Code error