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

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     |
 ------

CodePudding user response：

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