Returning ids of a table where all values of other table exist with this id using all() or exists()

Question

I have three tables with following data

Table 3 :

Table1_id        Table2_id
1                1
1                2
1                3
2                1
2                3
3                2

Table 2 :

Table2_id        Name
1                A
2                B
3                C

Table 1 :

Table1_id        Name
1                P
2                Q
3                R

I have a problem where I need to return all table1_id's which have an entry for all Table2_ids's in Table 3.
ie. I want my output to be

Table1_id
1

I found a solution using count(). But is there a way to use all() or exists() to solve the query?

Answer 1

Using NOT IN with excluding LEFT JOIN in a subselect with a CROSS JOIN

select *
from table1
where Table1_id not in (
    select t1.Table1_id
    from table1 t1
    cross join table2 t2
    left join table3 t3 using (Table1_id, Table2_id)
    where t3.Table1_id is null
)

VS using COUNT()

select table1_id 
from table3 
group by table1_id 
having count(1) = (select count(1) from table2)

Explanation:

The CROSS JOIN

    select t1.Table1_id
    from table1 t1
    cross join table2 t2

represents how table3 would look like, if every item from table1 would be related to every item from table2.

A (natural) left join with table3 will show us which relations really exists. Filtering by where t3.Table1_id is null (excluding LEFT JOIN) we get the missing relations. Using that result for the NOT IN clause, we get only table1 items that have no missing relation with table2.

Answer 2

You can use the following query:

SELECT DISTINCT t1.*
FROM Table2 AS t2
CROSS JOIN Table1 AS t1
WHERE NOT EXISTS (SELECT 1
                  FROM Table3 AS t3
                  WHERE t1.Table1_id = t3.Table1_id AND        
                        t2.Table2_id = t3.Table2_id)

to get Table1 records not having a complete set of entries from Table2 in Table3. Then use NOT IN to get the expected result.