CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
Sanoob
Sanoob

Reputation: 2474

MySQL to select row with unique column value comparing with other columns

I don't know how to explain this, I have a table with three column and I need to select rows with column value which is not repeated in other columns. 

Table:

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(45) NOT NULL,
  `col2` varchar(45) NOT NULL,
  `col3` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `new_table` (`col1`, `col2`, `col3`) VALUES ('red', 'blue', 'red');
INSERT INTO `new_table` (`col1`, `col2`, `col3`) VALUES ('white', 'red', 'yellow');
INSERT INTO `new_table` (`col1`, `col2`, `col3`) VALUES ('red', 'red', 'red');
INSERT INTO `new_table` (`col1`, `col2`, `col3`) VALUES ('yellow', 'blue', 'black');

I tried with xor in where statement. 

SELECT * FROM new_table WHERE
        col1 LIKE '%red%'
        xor col2 LIKE '%red%'
        xor col3 LIKE '%red%';

But I didn't get desired output 

Output of this code

ID  COL1    COL2    COL3
2   white   red     yellow
3   red     red     red

Output which I need 

ID  COL1    COL2    COL3
2   white   red     yellow

And what if I have N-Number of columns, I know this isn't a best database design.But just for knowledge.
SQLFiddle

Upvotes: 1

Views: 253

Answers (1)

Milan Zavišić
Milan Zavišić

Reputation: 301

Something like this:

SELECT * 
FROM new_table
WHERE CONCAT( col1,  ' ', col2,  ' ', col3 ) LIKE  "%red%"
AND col1 <> col2
AND col2 <> col3
AND col1 <> col3;

Upvotes: 2

Related Questions