Inclusive AND with an exclusive OR search

[jgabbai]

Hi,

Lets say I have a many to many cross-referenced table

b | t
-----
A | 1
A | 2
A | 3
A | 4
B | 1
B | 2
C | 3
C | 4
D | 2
D | 3
E | 1
E | 3
E | 4

If I want to get all cases of b which include 1, 3 and 4 I will use:

SELECT b FROM t AS t1, t AS t2, t AS t3 WHERE t1.b = t2.b AND t3.b =
t1.b AND t2.b = t3.b AND t2.a = 2 AND t1.a = 1 AND t3.a = 3

This will return A and E.

This is an AND search. How do I change this to include exclusive terms.
For example if I want any result that has 2 AND 3 but NOT 1 OR 4. Note
that the exclusive term is an OR not an AND.

This would return D

Many thanks!

[Bill Karwin]

jg*****@gmail.com wrote:

SELECT b FROM t AS t1, t AS t2, t AS t3 WHERE t1.b = t2.b AND t3.b =
t1.b AND t2.b = t3.b AND t2.a = 2 AND t1.a = 1 AND t3.a = 3

This is an AND search. How do I change this to include exclusive terms.
For example if I want any result that has 2 AND 3 but NOT 1 OR 4. Note
that the exclusive term is an OR not an AND.

In boolean algebra, BUT means the same thing as AND. ;-)

WHERE t2.a = 2 AND t1.a = 1 AND t3.a = 3
AND NOT (t1.a = 1 OR t2.a = 4)

Also, I note that your join conditions are redundant. If t1.b = t2.b
and t1.b = t3.b, then t2.b = t3.b must be true, and it is not necessary
to express the condition. Though the MySQL optimizer may be able to
factor it out.

Regards,
Bill K.

[jgabbai]

Hi,

Many thanks for your suggestions. I have tried the AND NOT, and it just
doesn't work. The returned query always includes the included terms,
regardless of the exclusion.