Multiple conditions

jc94062

I'm trying to produce a query where several things have to be true, before anyone jumps in with AND and OR its not that simple.

SELECT * FROM table WHERE something='1' OR something='2' AND thing='foo1' OR
thing='foo2'

The problem is that all results to that must contain only those rows where something is 1 or 2, AND where thing foo1 or foo2.

Currently of course it gives me all results where any of them are correct, I want something like the below (which I can't use due to an obvious parse error)

Originally trying...

$query = mysql_query("SELECT * FROM table WHERE something='1' OR something='2' AND thing='foo1' OR
thing='foo2'",$db);

With brackets trying

$query = mysql_query "SELECT * FROM table WHERE (something='1' OR something='2') AND (thing='foo1' OR
thing='foo2')",$db;

Thanks for any help anyone!

Anon

I had the same problem while writing a db search. After searching and searching I found the only way arround this was to write the condition in the long way:
(something='1') AND (thing='foo1') OR
(something='2') AND (thing='foo2') OR
(something='1') AND (thing='foo2') OR
(something='2') AND (thing='foo1')
What MySQL needs is an exclusive OR (XOR) but it doesn't yet and this might be the only way.

Anon

You didn't try ^ did you.

jc94062

I'll give it a go, but its kinda difficult cause theres more than 2 factors involved.

Time to write a LONG LONG select!

jc94062

I have no clue why that would work anyway, and it didn't, so I tried double brackets...

Heres the whole actual thing...

$hcroster = mysql_query "SELECT * FROM rsroster LEFT JOIN roster ON rsroster.zonename=roster.zonename WHERE ((roster.status='Active') AND (rsroster.position='13')) OR ((roster.status='Active') AND (rsroster.position='14')) OR ((roster.status='Active') AND (rsroster.position='15')) ORDER BY rsroster.position DESC, rsroster.zonename",$db;

I first tried

$hcroster = mysql_query "SELECT * FROM rsroster LEFT JOIN roster ON rsroster.zonename=roster.zonename WHERE (roster.status='Active') AND (rsroster.position='13') OR (roster.status='Active') AND (rsroster.position='14') OR (roster.status='Active') AND (rsroster.position='15') ORDER BY rsroster.position DESC, rsroster.zonename",$db;

which of course, also did not work because mysql can't interpret what I mean.

Anon

hello, I hope you can help, I have a join statements running over 3 tables and would definitely need an XOR ... but having none ...

SELECT id, group_name, user_name FROM Groups, Users
LEFT JOIN Bugs.ASSIGNED_GROUP ON Groups.group_id WHERE Bugs.ASSIGNED_USER = 0 AND STATE_ID != 5 OR
LEFT JOIN Bugs.ASSIGNED_USER ON Users.user_id WHERE Bugs.ASSIGNED_USER != 0 AND STATE_ID != 5

this is the statement that explains what I need, pity mysql doesn't understand me.

I'd really appreciate any help I can get

Anon

I finally found a way, which is working for me so far:
(a|b)&(!(a&b))
This works as an 'Exclusive OR'.
Hope this helps!