Subquery checks multiple columns

[Dani]

I'm trying to select rows from a table that meet a bunch of criteria, but one of the criteria is that a row doesn't already exist with two matching columns both being true at the same time. For example, suppose table t1 has columns: a, b, c and d

Here's what I'm trying, but it's giving me a syntax error ...

SELECT *
FROM t1
WHERE a, b NOT IN
    (
        SELECT a, b
        FROM t1
        WHERE c = 5 AND d = 6
    )
AND b NOT IN (1, 2, 3, 4)

[simplypixie]

I might be totally worng, but would you not need to check for both a and b separately

SELECT *
FROM t1
WHERE a NOT IN
    (
        SELECT a
        FROM t1
        WHERE c = 5 AND d = 6
    )
AND b NOT IN
    (
        SELECT b
        FROM t1
        WHERE c = 5 AND d = 6
    )
AND b NOT IN (1, 2, 3, 4)

[pritaeas]

Am not entirely sure, but I think a subquery requires an alias too.

[BitBlt]

Questions like this are notoriously hard to solve because of two things:
1. The querent doesn't explain the situation fully or properly.
2. The querent doesn't explain the expected result fully or properly.

So, for example, the original question contains the following line:

...one of the criteria is that a row doesn't already exist with two matching columns both being true at the same time

So your scenario is unclear...are you looking for rows that have value A (irrespective of B) where C=5 and D=6, and rows that have value B (irrespective of A) where C=5 and D=6, and if you get rows in one or the other but not both, you're fine? Or are you looking for rows where A = our original A, and B = our original B, and C=5 and D=6, and if you get a hit, don't include the row?

You have to be careful when you're doing subselects into the same table...you could get a situation where you don't have enough criteria to prevent a row from testing against itself.

At the risk of completely misunderstanding the scenario, have you considered using a left join? It would look like this, depending on the scenario:

-- If you want both A and B to not match the same row...
select * 
from t1 MainTable
left join t1 JoinTable
on MainTable.a = JoinTable.a
and MainTable.b = JoinTable.b
and JoinTable.c  = 5
and JoinTable.d = 6
where MainTable.b not in (1, 2, 3, 4) 
and JoinTable.a is null

-- If it is okay for A to match, or B to match, but NOT BOTH...
select * 
from t1 MainTable
left join t1 JoinTableA
on MainTable.a = JoinTableA.a
and JoinTableA.c  = 5
and JoinTableA.d = 6
left join t1 JoinTableB
and MainTable.b = JoinTableB.b
and JoinTableB.c  = 5
and JoinTableB.d = 6
where MainTable.b not in (1, 2, 3, 4) 
and not (JoinTableA.a is not null and JoinTableB.a is not null)

So, as you can see, the explanation you give can be interpreted in multiple ways.

If the hints I've given help you solve your problem, great. If not, perhaps a more realistic or complete explanation might help us to help you.

[Dani]

Hi guys, thanks so much for your responses. I wrote the question at like 3 am when I was frustrated and exhausted, so my apologies.

I was trying to select rows of a table in which two columns both don't have particular values at the same time. (Plus other criteria is true).

Simplypixie, I need both a and b to not be certain values at the same time, but they can each be those values individually, as BitBit suggests. (Sorry for being unclear BitBit!)

I've never done a LEFT JOIN on the same table before ... a tad confusing for me to wrap my mind around, but I think I got it, and I'll give it a shot.

Right now what I'm just doing is something like WHERE a NOT IN (SELECT DISTINCT a ... and just giving up on b altogether. I'm fetching fewer rows than I should be (since I just want to exlude ones where a and b are both certain values), but in my given circumstance (which is kinda harder to explain what I need it for than what I need), it's okay not to fetch all possibilities as long as the ones I am fetching are accurate.

[simplypixie]

Oh well, it might have helped :)

[BitBlt]

No worries...everything looks worse at 3am. Been there, done that so many times! That's why they pay us the big bucks, right? :-)

Be sure to let us know if you get it figured out...I'm curious to see how this resolves.