We help IT Professionals succeed at work.

sql - check multiple values not in other table

victoriaharry
on
Hi Guys,

I need to run a query against an IBM DB2 database which checks multiple columns against another table. I will try and explain this the best I can
There are values from 3 columns which make up an agreement so lets call them a,b & c
These values may also exist in another table under 3 different columns.
What I need to do is have the first query only return results where a,b,c as a group do not appear in the same row in the other table.
I have the query below which doesn't work but might better explain what I'm trying to do
I hope this makes sense. I have not put the full query in here as it is quite lengthy and may add more confusion. The part that is causing me grief is the try to check that the group of values don't exist in the other table
Is there a way to do this?

SELECT a,b,c,d,e
FROM table_Y
WHERE a,b,c
NOT IN
(SELECT f,g,h FROM table_Z)

Another thing worth mentioning. In the example abovethe values might appear in a row in the second table with different values (ie a,t,p). I only want a list of rows where a,b,c exist together  in the same row not with different values

Thanks

Gav
Comment
Watch Question

Software Developer / Database Administrator
Commented:
You're close.

SELECT a,b,c,d,e
FROM table_Y
WHERE (a,b,c)
NOT IN
(SELECT f,g,h FROM table_Z)

HTH!

Open in new window

Dave FordSoftware Developer / Database Administrator

Commented:
The query above will work, but it's probably more efficient to use an EXCEPTION JOIN.

select Y.*
  from table_Y
 exception join table_Z Z
    on Y.a = Z.a
   and Y.b = Z.b
   and Y.c = Z.c

Open in new window