CODEWITHSUNDEEP
sqlsqlite
notAChance
notAChance

Reputation: 1430

Get all rows which match an ID's subset of row's columns from same table

I have one table:

+------+------+
|ID    |DESC  |
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |A     |
+------+------+
|3     |C     |
+------+------+
|4     |B     |
+------+------+

I want to be able to get all rows which match any of ID 1's DESC's. So expected output would be:

+------+
|ID    |
+------+
|1     |
+------+
|2     |
+------+
|4     |
+------+

Upvotes: 0

Views: 77

Answers (1)

aitchdot
aitchdot

Reputation: 504

SELECT %table%.ID
FROM %table% t1
JOIN %table% t2 ON t2.DESC = t1.DESC
WHERE t2.ID = 1;

Replace %table% with the name of your table

This selects the ID column from your table and perfoms a self join on the condition that the DESC column matches the DESC column of the first instance.

More about the self join here

Upvotes: 3

Related Questions