Say I have a simple table
Foo with columns
I’m trying to select all customers from
CUSTOMER table who have both
App 1 and
App 2 in
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
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.