CODEWITHSUNDEEP
mysql
Om Solari
Om Solari

Reputation: 217

Select Pairs in MySQL

Have a table where certain rows come in couples which have a matching GUID. Just wondering how to SELECT all data from the table but ONLY if the rows exist as a couple with a matching GUID.

Upvotes: 0

Views: 3622

Answers (3)

peterm
peterm

Reputation: 92795

Try

SELECT t.* 
  FROM Table1 t JOIN
(
  SELECT guid
    FROM Table1
   GROUP BY guid
  HAVING COUNT(*) = 2
) q ON t.guid = q.guid

Here is SQLFiddle demo

Upvotes: 1

Aguardientico
Aguardientico

Reputation: 7779

Try something like this:

SELECT t1.*
FROM
   table t1
 , table t2
WHERE
     t1.guid = t2.guid
 AND t1.id <> t2.id
;

table: your table name

id: some field that you know is different for both rows

Upvotes: 2

fthiella
fthiella

Reputation: 49049

You can use a query like this:

SELECT *
FROM yourtable
WHERE GUID IN (SELECT GUID FROM yourtable GROUP BY GUID HAVING COUNT(*)=2)

The subquery will return all GUIDs that appears exactly twice, the outer query will return all rows associated to those GUIDs.

Please see fiddle here.

Upvotes: 2

Related Questions