CODEWITHSUNDEEP
mysqlrows
madkitty
madkitty

Reputation: 1675

How to retrieve match records across multiple tables in MySQL

I have two identical tables such as : 

table1
col1    col2    col3    col4    col5

table2
col1    col2    col3    col4    col5

I want to compare table 1 and 2 and find identical rows (col1, col2, col3, col4 etc..) between the 2 tables.

I think we need to use vtable or something similar ..

I tried 

SELECT * FROM TABLE1 WHERE COL1, COL2, COL3, COL4 IN 
        (SELECT COL1, COL2, COL3, COL4 FROM TABLE2);

It doesn't work .. help please :)

Upvotes: 2

Views: 3479

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

SELECT * 
FROM TABLE1 t
WHERE EXISTS
      ( SELECT *
        FROM TABLE2 tt
        WHERE (COL1, COL2, COL3, COL4) 
            = (t.COL1, t.COL2, t.COL3, t.COL4)
      )
;

Upvotes: 3

ZZ-bb
ZZ-bb

Reputation: 2167

How about: 

SELECT table1.* FROM table1
  INNER JOIN table1 ON table2.col1 = table1.col1
  WHERE table1.col2 = table2.col2 AND table1.col3 = table2.col3
    AND table1.col4 = table2.col4 AND table1.col5 = table2.col5

Upvotes: 1

Marc
Marc

Reputation: 11633

Oracle syntax, which I'm sure is nearly identical in MySql:

 SELECT *
   FROM TABLE1, TABLE2
  WHERE TABLE1.COL1 = TABLE2.COL1
    AND TABLE1.COL2 = TABLE2.COL2
    AND TABLE1.COL3 = TABLE2.COL3
    AND TABLE1.COL4 = TABLE2.COL4

There might be a more elegant and clever way, but that'll find your duplicates.

Upvotes: 1

Related Questions