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

I am writing this dynamically using PHP (I don't need PHP code). Also
- it would be great if the same expression could handle a case where
if there was no inclusive element, just an exclusive element, all
releveant items would appear. For example if I want to exclude 1 OR 2,
the answer would be C

Many thanks!

Which version of MySQL are you using?

Currently have access to 

4.0.20

4.1.10a-nt

Find letters that have 2 AND 3 but NOT 1 OR 4:

SELECT t2.b
FROM t t2
JOIN t t3 ON t3.b = t2.b AND t3.a = 3
LEFT JOIN t tx ON tx.b = t2.b AND tx.a IN (1, 4)
WHERE t2.a = 2
AND tx.a IS NULL


Find letters that have 2 AND 3 AND 4 AND 5 but NOT 6 OR 7 OR 8:

SELECT t2.b
FROM t t2
JOIN t t3 ON t3.b = t2.b AND t3.a = 3
JOIN t t4 ON t4.b = t2.b AND t4.a = 4
JOIN t t5 ON t5.b = t2.b AND t5.a = 5
LEFT JOIN t tx ON tx.b = t2.b AND tx.a IN (6, 7, 8)
WHERE t2.a = 2
AND tx.a IS NULL


Find letters that does NOT have 6 OR 7 OR 8:

SELECT DISTINCT t2.b
FROM t t2
LEFT JOIN t tx ON tx.b = t2.b AND tx.a IN (1, 2)
WHERE tx.a IS NULL

Find letters that has 1 AND 2 AND 3:

SELECT t.b, COUNT(*)
FROM t 
WHERE t.a IN (1, 2, 3)
GROUP BY t.b
HAVING COUNT(DISTINCT t.a) = 3

The "3" in the HAVING condition is the number of a values to include


Find letters that have 2 AND 3 AND 4 AND 5 but NOT 6 OR 7 OR 8:

SELECT t.b
FROM t 
LEFT JOIN t tx ON tx.b = t.b AND tx.a IN (6, 7, 8)
WHERE tx.a IS NULL
 AND t.a IN (2, 3, 4, 5)
GROUP BY t.b
HAVING COUNT(DISTINCT t.a) = 4

The "4" in the HAVING condition is the number of a values to include