MYSQL syntax for OR?

Question

I have a mysql table with 2 columns and a set of values called MAIN. table1:

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.

Berry Langerak · Accepted Answer · 2011-06-17 08:34:38Z

4

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 ;)

answered Jun 17, 2011 at 8:34

Berry Langerak

18.9k5 gold badges49 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jensgram Over a year ago

+1 This is exactly what I was digging up. From the docs: a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

David19801 Over a year ago

I've gone with this. Thanks! I will add, Thiefmaster's implode idea for the php array is also relevant.

Berry Langerak Over a year ago

@David19801 Oh, yes, definitely, just figured you knew this already, as it's not explicitly stated in your question. ;)

ThiefMaster · Accepted Answer · 2011-06-17 08:31:55Z

1

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.

answered Jun 17, 2011 at 8:31

ThiefMaster

320k85 gold badges608 silver badges648 bronze badges

Comments

cutsoy · Accepted Answer · 2011-06-17 08:33:32Z

0

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)

answered Jun 17, 2011 at 8:33

cutsoy

10.3k4 gold badges43 silver badges59 bronze badges

Comments

heximal · Accepted Answer · 2011-06-17 08:33:55Z

0

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))

answered Jun 17, 2011 at 8:33

heximal

10.5k5 gold badges49 silver badges73 bronze badges

Collectives™ on Stack Overflow

MYSQL syntax for OR?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related