Search code examples
mysqldatabaserdbms

Check multiple values in multiple columns mysql

I have database structure like one shown in following image. My table name is compare_analytics

database structure

I know how to check a single value against multiple columns. Example like one below:

SELECT * FROM `compare_analytics` WHERE '1MB20D-060' IN (igbt1,igbt2,igbt3)

I want to check multiple columns against multiple values. Like, I want to select all rows in which IRG4BC30FD and IRG4BC30FD igbt are present their position in row doesn't matter. They can be in igbt1, igbt2 or igbt3 on any of the column. Query should select rows with id 4,5, and 6 ideally because these values are present in these rows.

I am looking for a query without OR clause, I know that can be achieved with OR clause, but there should be some shorter way to do this.

Solution 
  •  DROP TABLE IF EXISTS compare_analytics;

 CREATE TABLE compare_analytics
 (igbt       VARCHAR(20) NOT NULL
 ,type INT NOT NULL
 ,PRIMARY KEY(igbt,type)
 );

 INSERT INTO compare_analytics VALUES
 ('1MB20D-040',1),
 ('1MB20D-050',2),
 ('1MB20D-060',3);

 SELECT * 
   FROM compare_analytics
  WHERE igbt IN ('1MB20D-040','1MB20D-060');

 +------------+------+
 | igbt       | type |
 +------------+------+
 | 1MB20D-040 |    1 |
 | 1MB20D-060 |    3 |
 +------------+------+