Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Inclusive AND with an exclusive OR search
September 14, 2005, 12:10 am
rate this thread
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
Re: Inclusive AND with an exclusive OR search
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.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum
- » ssh on command line: force using a group size (prime size) of 1024 (and no...
- — The site's Newest Thread. Posted in » Secure Shell Forum