CODEWITHSUNDEEP
sqlsql-servert-sql
xLokos
xLokos

Reputation: 109

Check multiple columns from one table against one column in another table

I have two tables on SQL Server.

First:

[id] ... [name1] ... [name2] [name3] ... [name4] ... [name5] [name6]

Second:

[id] ... [name] ...

I would like to select all rows from the first table, where all the names exists in the second table. I know there is a way to do this with 6 joins, but is there any "prettier" way?

SELECT A.*
FROM FirstTable A
    LEFT JOIN SecondTable B ON A.[Name1] = B.[Name]
    LEFT JOIN SecondTable C ON A.[Name2] = C.[Name]
    ...
WHERE
    B.[Name] IS NOT NULL
    AND C.[Name] IS NOT NULL
    ...

Bonus question, is there a way to instead selecting only the rows that contain valid names, to select all of them and add another column to the select, which would point out which of the names are invalid?

Upvotes: 0

Views: 1340

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use apply:

select t1.*
from table1 t1 cross apply
     (select count(*) as cnt
      from (values (name1), (name2), (name3), (name4), (name5), (name6)
           ) v(name) join
           table2 t2
           on t2.name = v.name
     ) v
where v.cnt = 6;

Your method can be marginally simplified by replacing the LEFT JOINs with INNER JOINs and removing the WHERE clause. The INNER JOINs require a match.

Upvotes: 1

Related Questions