Amulya Sharma Amulya Sharma - 2 months ago 16
SQL Question

SELECT common entities only based on different corresponding entities

3 Tables

Client -

CID Name
1 Ana
2 Bana
3 Cana


ClientProgram (Bridge Table) -

CID PID
1 4
1 5
1 8
2 10


Program -

PID Program
4 X
5 Y
8 Z
10 G


Desired Output:

Name Program
Ana X
Ana Y


I want to extract only those
Clients
which are common/exist in different
Programs
I choose (say X and Y in this case)

Query attempt:

SELECT
C.Name
,P.Program
FROM ClientProgram CP
INNER JOIN Client C
ON CP.CID=C.CID
INNER JOIN Program P
ON CP.PID=P.PID
INNER JOIN ClientProgram CP1
ON CP.CID=CP1.CID

WHERE P.Program = 'X' OR P.Program = 'Y'
AND CP.CID = CP1.CID


This however doesn't pulls in all clients and not only those which exist in multiple programs.

Answer
;WITH cte AS (
    SELECT
       c.Name
       ,p.Program
       ,COUNT(*) OVER (PARTITION BY c.CID) as ProgramCount
    FROM
       Program p
       INNER JOIN ClientProgram cp
       ON p.PID = cp.PID
       INNER JOIN Client c
       On cp.CID = c.CID
    WHERE
       p.Program IN ('X','Y')
)

SELECT Name, Program
FROM
    cte
WHERE
    ProgramCount > 1

The use of COUNT(*) over will be a problem if PID is not unique in Programs or if the combination of CID to PID in ClientProgram is not unique. However I would assume uniqueness based on what I see.

If not you can go a route like this:

;WITH cte AS (
    SELECT
       cp.CID
    FROM
       Program p
       INNER JOIN ClientProgram cp
       ON p.PID = cp.PID
    WHERE
       p.Program IN ('X','Y')
    GROUP BY
       cp.CID
    HAVING
       COUNT(DISTINCT p.PID) > 1
)

SELECT
    c.Name
    ,p.Program
FROM
    cte t
    INNER JOIN Client c
    ON t.CID = c.CID
    INNER JOIN ClientProgram cp
    ON t.CID = cp.CID
    INNER JOIN Program p
    ON cp.PID = p.PID
    AND p.Program IN ('X','Y')