MYSQL syntax for OR?

I have a mysql table with 2 columns and a set of values called MAIN. ta开发者_Python百科ble1:

col1, int(11)
col2, int(11)

Set of values: MAIN: 1,3,4

col1 and col2 contain random integers between 1 and 10 for 1000 rows. Then, any rows where col1 and col1 contain the SAME integer are removed.

I'm trying to pull those rows from table1 where: col1 contains values from MAIN and col2 does not. AND col2 contains values from MAIN and col1 does not.

in invented pseudo code I have:

mysql_query("SELECT col1,col2 
             FROM table1 
             WHERE (col1 contains values from MAIN and col2 does not) 
             OR (col2 contains values from MAIN and col1 does not) ");

Any ideas on what the correct syntax is for this?

P.S. The set of values starts as a PHP array.

---

Perhaps you're looking for the XOR logical operator?

SELECT
  col1,
  col2
FROM
  table1
WHERE
  ( col1 IN ( 1, 3, 4 ) XOR col2 IN ( 1, 3, 4 ) );

Small example of how XOR works:

SELECT true XOR true; -- false
SELECT true XOR false; -- true
SELECT false XOR true; -- true
SELECT false XOR false; -- false

That's pretty much your whole ( col1 in ( MAIN ) and col2 not in main ) OR ( col1 not in ( MAIN ) and col2 in ( main ) ), but written more succinct ;)

---

WHERE (col1 IN (MAIN) AND col2 NOT IN (MAIN)) OR (col1 NOT IN (MAIN) AND col2 IN (MAIN))

MAIN should be your list, of, values of course.

If your PHP array contains only integers, you can use implode(',', $array) to get the proper value list to use in the IN() criterion.

---

Try this:

SELECT col1, col2 FROM table1 WHERE (col1 IN MAIN and col2 NOT IN MAIN) OR (col2 IN MAIN AND col1 NOT IN MAIN)

---

seems like you need smth liek this

SELECT 
  col1,col2 
FROM 
  table1 
WHERE 
 (col1 in (1,2,3) and col2 not in (1,2,3)) or
 (col1 not in (1,2,3) and col2 in (1,2,3))