CODEWITHSUNDEEP
mysqlsql
Rhs
Rhs

Reputation: 3318

How to check if one row in one table is in the other table using mysql?

I have two tables in MySQL. Both of them have two columns. How to check if one row in one table is in the other table using MySQL?

For example,

table 1:
a, c
b, a
d, f


table 2:
a, k
c, a
g, m
c, l

How can I check if each row from table 1: (a,c) (b,a) (d,f) exist in table 2 and then create a new column in table 1 containing the times each row shows up in table 2? The order doesn't matter here, so (a,c) is the same as (c,a). So in this case, one row (a,c) in table 1 match one time in table 2.

Upvotes: 0

Views: 112

Answers (2)

Jack
Jack

Reputation: 66

SELECT  a.col1, a.col2, COUNT(b.col1)
FROM table1 a LEFT JOIN table2 b
ON (a.col1 = b.col1 OR a.col1 = b.col2) 
AND (a.col2 = b.col1 OR a.col2 = b.col2)
GROUP BY a.col1, a.col2

http://sqlfiddle.com/#!2/89c17/1

Upvotes: 2

John Woo
John Woo

Reputation: 263683

use LEFT JOIN so all record of table1 will be shown even if it has no records on table2

SELECT  a.col1, a.col2, COUNT(b.col1)
FROM    table1 a
        LEFT JOIN table2 b
            ON a.col1 = b.col1 AND
                a.col2 = b.col2
GROUP BY a.col1, a.col2

Upvotes: 2

Related Questions