(SQL) Select rows only where a column's values are found in another column?-CodePudding

Is it possible to filter rows where one column only has values that match ones found in another column, but without using subselect?

Ex:

All rows

Selected rows

Filtered to only rows with column values that were referenced in the pointer column.

CodePudding user response：

Yes, by doing an INNER JOIN with the same table, matching the value of the different columns and keeping the results unique with DISTINCT (since there could be more than one pointers to the same name):

SELECT DISTINCT a.*
FROM mytable a INNER JOIN mytable b ON a.name = b.pointer

CodePudding user response：

Here You Go, using selg join:

Create table #temp( name varchar(200), ColumnA int, PointerColumnB int)

Insert into #temp values('NAMEA',1,null),('NAMEB',2,3),
('NAMEC',3,3),('NAMED',4,null),('NAMEE',5,4),('NAMEF',6,7),('NAMEG',7,null)

Select distinct x.Name
from #temp x
join #temp y on x.ColumnA = y.PointerColumnB