SQL select from a table if 2 records exist-CodePudding

Say I have a simple table Foo with columns Customer_ID and App_ID

I'm trying to select all customers from CUSTOMER table who have both App 1 and App 2 in Foo table.

I can do

SELECT C FROM CUSTOMER C 
INNER JOIN FOO F ON 
F.CUSTOMER_ID = C.ID
INNER JOIN APP A ON 
A.ID = F.APP_ID
WHERE A.NAME = 'App 1`

But that only gives me results when a customer has 1 app, i want results only if both apps are in Foo.

CodePudding user response：

I would phrase this using exists logic:

SELECT C.*
FROM CUSTOMER C 
INNER JOIN FOO F
    ON F.CUSTOMER_ID = C.ID
WHERE
    EXISTS (SELECT 1 FROM APP A WHERE A.ID = F.APP_ID AND A.NAME = 'App 1') AND
    EXISTS (SELECT 1 FROM APP A WHERE A.ID = F.APP_ID AND A.NAME = 'App 2');

If you wanted to this via joining, then you would need two joins to the APP table, e.g.

SELECT DISTINCT C.*
FROM CUSTOMER C 
INNER JOIN FOO F
    ON F.CUSTOMER_ID = C.ID
INNER JOIN APP A1
    ON A1.ID = F.APP_ID
INNER JOIN APP A2
    ON A2.ID = F.APP_ID
WHERE
    A1.NAME = 'App 1' AND
    A2.NAME = 'App 2';

Note that I am using a distinct select above, because joining to APP twice may generate duplicate records. Removing possible duplicates represents an extra step in the join approach which won't happen when using exists.