Need SQL Help please

[Bri123456]

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

 

[carp]

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);

 

[contactop]

hope the above code by carp works for u.

 

[topher0303]

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.