CODEWITHSUNDEEP
phpmysqlsql
Tarun Bhardwaj
Tarun Bhardwaj

Reputation: 137

Mysql query to find records that are common in multiple columns across multiple tables.

Table1

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

Table2

- | a2 | b2 | C2 | d2 | e2 |
- |----+----+----+----+----|
- | 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 |

Results i want:

2,3

Because 2 and 3 are common in all the columns across these two tables.

I have tried :

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

It works good for table1 but how to join more tables.

Upvotes: 0

Views: 1124

Answers (2)

majidarif
majidarif

Reputation: 20065

Well you can start with this fiddle with 10 rows.

Where you will count it to equal how many rows you are comparing.
Your example has table1 6 rows, table2 6 rows. 12 rows for example is...

SELECT
    a.value,
    COUNT(a.value) as count
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 

    UNION ALL

    SELECT a2 AS value
    FROM table2

    UNION ALL

    SELECT b2 AS value
    FROM table2

    UNION ALL

    SELECT C2 AS value
    FROM table2     

    UNION ALL

    SELECT d2 AS value
    FROM table2

    UNION ALL

    SELECT e2 AS value
    FROM table2


) a
GROUP BY value
HAVING count >= 12
ORDER BY count DESC

to be more dynamic on the row count you can also try:

HAVING count >= ((SELECT COUNT(*) as t1 FROM table1) + (SELECT COUNT(*) as t2 FROM table2))

update

Something similar to your approach is: http://sqlfiddle.com/#!2/56d52/4

SELECT t.value, COUNT(t.value) as count
FROM
(
SELECT DISTINCT c1.a AS value FROM  `table1` c1 
JOIN  `table1` c2 ON ( c1.a = c2.b )  
JOIN  `table1` c3 ON ( c2.b = c3.c )  
JOIN  `table1` c4 ON ( c3.c = c4.d )  
JOIN  `table1` c5 ON ( c4.d = c5.e )

UNION ALL

SELECT DISTINCT c1.a2 AS value FROM  `table2` c1 
JOIN  `table2` c2 ON ( c1.a2 = c2.b2 )  
JOIN  `table2` c3 ON ( c2.b2 = c3.C2 )  
JOIN  `table2` c4 ON ( c3.C2 = c4.d2 )  
JOIN  `table2` c5 ON ( c4.d2 = c5.e2 )
) t
GROUP BY t.value
HAVING count = 2

Upvotes: 0

Federico J.
Federico J.

Reputation: 15912

It's exactly the same:

SELECT DISTINCT c1.a FROM  `t1` c1 
JOIN  `t1` c2 ON ( c1.a = c2.b )  
JOIN  `t1` c3 ON ( c2.b = c3.c )  
JOIN  `t1` c4 ON ( c3.c = c4.d )  

JOIN  `t2` c5 ON ( c4.d = c5.a )  
JOIN  `t2` c6 ON ( c5.b = c6.c )  
JOIN  `t2` c7 ON ( c6.c = c7.d )  
LIMIT 0 , 10

Check this fiddle: http://sqlfiddle.com/#!2/46ade/6

Upvotes: 1

Related Questions