Mysql query to find records that are common in multiple columns

Question

Table

   | a  | b  | C  | d  | e  |
   |----+----+----+----+----|
   | 1  | 2  | 3  | 2  | 9  |
   | 2  | 3  | 2  | 3  | 5  |
   | 3  | 8  | 4  | 4  | 2  |
   | 4  | 9  | 8  | 5  | 3  |
   | 5  | 1  | 9  | 7  | 1  |
   | 6  | 12 | 10 | 9  | 10 |

I want in result:

2,3

Because 2,3 are common in all columns.

check this http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql — codemania, Mar 14 '14 at 13:18

score 0 · Answer 1 · answered Mar 14 '14 at 13:07

I don't know it this is the best way. But something like this:

SELECT
    value
FROM
(
    SELECT a as value FROM table1 UNION ALL
    SELECT b as value FROM table1 UNION ALL
    SELECT c as value FROM table1 UNION ALL
    SELECT d as value FROM table1 UNION ALL
    SELECT e as value FROM table1
) AS tbl
GROUP BY value
HAVING COUNT(value) > 1

This will result to:

value
2
3

score 0 · Answer 2 · answered Mar 14 '14 at 13:12

0

Havn't tryed this, but it should do the trick:

SELECT t1.a
FROM the_table t1
  JOIN the_table t2 ON t1.a=t2.b
  JOIN the_table t3 ON t2.b=t3.c
  JOIN the_table t4 ON t3.c=t4.d
  JOIN the_table t5 ON t4.d=t5.e

answered Mar 14 '14 at 13:12

user1517081

965
2
11
30

score 0 · Accepted Answer · answered Mar 14 '14 at 13:15

SELECT DISTINCT c1.a FROM  `in_all_cols` c1 
JOIN  `in_all_cols` c2 ON ( c1.a = c2.b )  
JOIN  `in_all_cols` c3 ON ( c2.b = c3.c )  
JOIN  `in_all_cols` c4 ON ( c3.c = c4.d )  
JOIN  `in_all_cols` c5 ON ( c4.d = c5.e )
LIMIT 0 , 30

Worked for me with this layout:

CREATE TABLE IF NOT EXISTS `in_all_cols` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `in_all_cols` (`a`, `b`, `c`, `d`, `e`) VALUES
(1, 2, 3, 2, 9),
(2, 3, 2, 3, 5),
(3, 8, 4, 4, 2),
(4, 9, 8, 5, 3),
(5, 1, 9, 7, 1),
(6, 12, 10, 9, 10);

Mysql query to find records that are common in multiple columns

3 Answers3