Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need SQL Help please

Status
Not open for further replies.

Bri123456

Programmer
Jan 3, 2002
30
CA
Hi, I have 2 tables both have a field that is Number(11) and I am trying to devise a list of rows in table 2 that do not have a matching field in table 1.

Table 1 Table 2
Field_x field_y


I need all field_y's that do not appear in field_x

if field_x has 3 values 221,223,222 and field_y has the fields 221,222,224 then i need my list to show 224. I realize that this may be easy but I need help on it. Thanks in advance for any assistance
 
SELECT fieldy FROM table1
MINUS
SELECT fieldx FROM table2;

OR

SELECT fieldy FROM table1
WHERE fieldy NOT IN (SELECT fieldx FROM table2);

OR

SELECT fieldy FROM table1 t1
WHERE NOT EXISTS (SELECT 'x' FROM table2
WHERE fieldx = t1.fieldy);
 
OR

SELECT fieldy
FROM table2 t2, table1 t1
WHERE t2.fieldy = t1.fieldx (+)
AND t1.fields IS NULL

carp's examples work as well, however, you will need to swap table1 and table2 in each. Bruce wanted additional rows that existed in table2, not table 1. Plus, fieldy exists in table2. The concept is accurate though.

Chris.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top