6

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?

Shear Plane
  • 124
  • 8
rcb_sp
  • 65
  • 5

3 Answers3

3

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.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • that's exactly what i was looking for . . Thank You for the answer and the explanation as well :) @paulspiegel – rcb_sp Mar 28 '16 at 10:04
  • I've been wondering if there is any way to use all() for that question :? – rcb_sp Mar 28 '16 at 19:55
  • 1
    "`NOT IN` is an alias for `<> ALL`." [Subqueries with ALL](https://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html) - So in fact, i'm alredy using `ALL()` :-) – Paul Spiegel Mar 28 '16 at 20:05
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.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • 1
    @PaulSpiegel I've commented that the query does not yield the expected result. You have to use `NOT IN` to get the required output. – Giorgos Betsos Mar 28 '16 at 09:56
  • Thanks for the answer :) But i would like to know what exactly does select 1 from . . mean? – rcb_sp Mar 28 '16 at 10:05
  • 1
    It doesn't mean anything actually. It's just a literal value returned and is all `NOT EXISTS` needs in order to be evaluated. – Giorgos Betsos Mar 28 '16 at 10:06
  • 2
    @rcb_sp, "MySQL ignores the SELECT list in such a subquery, so it makes no difference" [Subqueries with EXISTS or NOT EXISTS](http://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html) – Paul Spiegel Mar 28 '16 at 10:16
0

Here is a solution using EXISTS and INNER JOIN.

SELECT DISTINCT t3_out.Table1_id FROM Table3 t3_out
WHERE EXISTS( SELECT 1
    FROM Table2 t2 INNER JOIN Table3 t3 ON t2.Table2_id = t3.Table2_id
    WHERE t3.Table1_id = t3_out.Table1_id
    HAVING COUNT(DISTINCT t2.Table2_id) = 3 )
Dylan Su
  • 5,975
  • 1
  • 16
  • 25