This week's book giveaways are in the iOS and Features new in Java 8 forums.
We're giving away four copies each of Barcodes with iOS: Bringing together the digital and physical worlds and Core Java for the Impatient and have the authors on-line!
See this thread and this one for details.
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

Win a copy of Barcodes with iOS this week in the iOS forum
or Core Java for the Impatient in the Java 8 forum!

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:
subject: SQL Query