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

## Recommended Answers

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 …``````

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 …

## All 6 Replies

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

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

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.

commented: Thanks!! :) Never thought of doing a left join on the same table +15

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.

Oh well, it might have helped :)

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.