Excluding rows based on Multiple Criteria 1

[moquif]

I am trying to get a record set that does not include values that are considered blocked. Normally I would do this:

SELECT Value_1, Value_2, Value_3, Value_4, Value_5
FROM tbData
WHERE Value_1 NOT IN (SELECT Value_1 FROM tbBlockedValues)

The problem is, tbBlockedValues has 2 fields and it has to match a row in tbData to block it. I cannot use several NOT statements because it would not fit the requirement that the two fields be in the same row. Each would appear in any row indepedent of each other.

I've tried joins, putting it into a temp table then deleting rows, but I'm out of ideas.

Thank you,
Jason

 

[carp]

I am not sure what you are trying to say. Could you provide us with some sample data and your desired output?

 

[swampBoogie]

SELECT Value_1, Value_2, Value_3, Value_4, Value_5
FROM tbData
WHERE (Value_1,Value_2) NOT IN 
(SELECT (Value_1,Value_2) FROM tbBlockedValues)

or if your DBMS does not support row expressions

SELECT Value_1, Value_2, Value_3, Value_4, Value_5
FROM tbData td
WHERE NOT exists 
(SELECT * FROM tbBlockedValues tb
  where tb.value_1 = td.value_1
   and tb.value_2 = td.value_2)

 

[PHV]

You may try this:
SELECT Value_1, Value_2, Value_3, Value_4, Value_5
FROM tbData
WHERE Value_1 || ',' || Value_2 NOT IN
(SELECT Value_1 || ',' || Value_2 FROM tbBlockedValues)

|| is the concatenation operator


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[moquif]

swampBoogie

Thank you very much for the code the NOT EXISTS turned out to be the key (row expressions is apparently not allowed in my DBMS which is MS Enterprise Manager Management Console v1.2). The application runs much, much faster when it can grab everything at once instead of doing it one parameter at a time.

Thanks again,
Jason