CODEWITHSUNDEEP
sqlsql-servert-sql
hofnarwillie
hofnarwillie

Reputation: 3660

Select rows from SQL table where ALL of a set of parameters exist in joined table

Given the following SQL Server schema:

CREATE TABLE #TableA (Id int);
CREATE TABLE #TableB (Id int, TableA_Id int, Status_Id int)
CREATE TABLE #Statuses (Id int)

SELECT
    *
FROM
    #TableA AS A
    INNER JOIN #TableB AS B
        ON A.Id = B.TableA_Id
    INNER JOIN #Statuses AS S
        ON B.Status_Id = S.Id

How can I get all the rows in TableA for which there are at least one entry of each of the rows in dbo.Statuses?

For example, in the following set of data only row 2 of #TableA should be returned:

#TableA
[1]
[2]
[3]
[4]

#Statuses
[1]
[2]
[3]

#TableB
[1][1][1]
[2][1][3]
[3][2][1]
[4][2][2]
[5][2][3]
[6][3][1]

Upvotes: 0

Views: 88

Answers (3)

Linger
Linger

Reputation: 15048

SQL Fiddle:

SELECT TableA_Id
FROM TableB
GROUP BY TableA_Id
HAVING COUNT(*) >= (SELECT COUNT(*) FROM Statuses);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this with a "simple" where clause. Assuming that the status column in #TableB only has valid status values:

select a.*
from #TableA a
where (select count(*) from statuses) = 
      (select count(distinct status) from #TableB b where b.TableA_Id = a.id);

You can relax this assumption. Here is one way:

select a.*
from #TableA a join
      (select count(*) as numstatuses from #statuses) s
where (select count(distinct b.status)
       from #TableB b join
            #statuses s
            on b.id = s.id
       where b.TableA_Id = a.id
      ) = numstatuses

Upvotes: 1

Lamak
Lamak

Reputation: 70638

SELECT B.TableA_Id
FROM #Statuses S
LEFT JOIN #TableB B
    ON B.Status_Id = S.Id
GROUP BY B.TableA_Id
HAVING COUNT(DISTINCT S.Id) = COUNT(DISTINCT B.Status_Id)

Upvotes: 1

Related Questions