jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Query" Watch "SQL Query" New topic

SQL Query

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
Hi !
I have 2 tables with the following structure:
Table 1: A | B | C
rec.1 a1| b1| c1
rec.2 a1| b2| c1
Table 2: B | C | D
b1| c1| d1
b2| c2| d2
Could someone help me with a query which finds all records from Table 2 with the pair (b,c) which are NOT present in Table 1.
(In above eg. it should return (b2, c2, d2) since the pair (b1, c1) is present in Table 1)
I'm sure its a simple query .. but can't seem to think of anything.
Thanks in advance,

[This message has been edited by Nair Anoop (edited September 19, 2001).]
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
You can do an outer join and part of the where is A is null
which would be all the records which did not have a match
This is assuming that null is not a valid value for column A
namita pa
Ranch Hand

Joined: Sep 06, 2001
Posts: 56
to make it a bit more simpler for you try this query...............
select table2.b, table2.c, table2.d from table2, table1 where not (table2.b=table1.b or table2.c=table.c);
i hope this would work as per your requirement
all the best

Nair Anoop
Ranch Hand

Joined: May 09, 2001
Posts: 68
Thanks Namita and Daniel for your help
I agree. Here's the link: http://aspose.com/file-tools
subject: SQL Query
It's not a secret anymore!