0

How can I query table 1 to give me records where id2 exists more than once in id1?

table 1
id1, id2
1,100
1,99
1,98
2,100
2,101
2,102

So the desired result would be

1,100
2,100
Rod
  • 14,529
  • 31
  • 118
  • 230
  • You may find 'group by id1' and 'having count(id2) >= 2' useful? The 'having' clause is the same as a 'where' clause but applies to 'groups' rather than individual rows. – Ryan Vincent Sep 14 '18 at 20:03
  • @ryanvincent If you group by `ID1` but try to return both `id1` and `id2` you will get a random `id2` value back for each distinct `id1` (in MySQL 5.6 or older). In MySQL 5.7 or newer you will just get an error. If you omit `id2` from the output and apply that `HAVING` clause you will end up with two records for `id1` in the return: `1` and `2` which is sort of useless. – JNevill Sep 14 '18 at 20:21

3 Answers3

0

Your best bet here is a subquery to get id2 values that show up more than once in your table. Then return records from the table that contain those id2 values. There's a few ways to do this.

Here is one using the IN clause and just hitting a list returned by a subquery:

SELECT id1, id2 
FROM table1 
WHERE id2 IN (SELECT id2 FROM table1 GROUP BY id2 HAVING count(*) > 1)

And another using a correlated subquery which may prove useful if your data is larger

SELECT id1, id2 
FROM table1 t1
WHERE EXISTS (SELECT id2 FROM table1 WHERE t1.id2 = id2 GROUP BY id2 HAVING count(*) > 1)
JNevill
  • 46,980
  • 4
  • 38
  • 63
-1

You can use the following query to determine if an id exists in more than one instance of another column:

select id1, id2
from table
group by id2
having count(*) > 1
Grant Miller
  • 27,532
  • 16
  • 147
  • 165
DanB
  • 2,022
  • 1
  • 12
  • 24
  • I don't believe this will return what OP is after. It will return `100` for id2 and, randomly, either `1` or `2` for `id1`. In Mysql 5.7 or newer it will throw an error unless they have flipped their ALWAYS_FULL_GROUP_BY setting off. [sqlfiddle of mysql5.6](http://sqlfiddle.com/#!9/f966e62/1) – JNevill Sep 14 '18 at 20:07
-1

You can use une a join with id2 like this:

SELECT 
    t1.id1
    ,t1.id2
FROM
    table1 as t1
inner join table1 as t2 on t2.id2 = t1.id2
Dranes
  • 156
  • 10
  • Not sure where you were going with this one, but this returns some oddball cross join, not the two records OP is after. – JNevill Sep 14 '18 at 20:09