SQL Retrieve rows that have all rows from other table

Question

I am facing some issues retrieving data using SQL in one specific scenario:

Suppose I have the following tables:

A (id, attr_a, attr_b);
B (id, attr_d, attr_e);
C (id_a, id_b);

As you can see, table C have FK referencing id from table A, and referencing id from table C.

I need to retrieve table A rows that for each A row relates to every B row.

In a real life scenario, assuming A means users, B means privileges, and C is the many to many entity that relates users with privileges, I would want to get only users that have ALL privileges

@user1490835 a and b dont have a relationship so I cannot use inner join, also, I want only A rows, that have all B id`s, so join would not work, it needs to be more restrict — Rogger Fernandes, Aug 29 '16 at 01:10
`a and b dont have a relationship` ... yes they do, through table `C`. — Tim Biegeleisen, Aug 29 '16 at 01:15

klin · Answer 1 · 2016-08-29T02:50:20.670

2

select A.*
from A
join C on id_a = id
group by id
having count(id) = (select count(*) from B)

There is no need to use a subquery because A.id is a primary key (or unique at least) as a column referenced by C.id_a.

edited Aug 29 '16 at 02:50

answered Aug 29 '16 at 01:22

klin

112,967
15
204
232

Tim Biegeleisen · Answer 2 · 2016-08-29T01:29:46.153

1

The following query should return all A records where, for a given ID, they match to every record in the B table. Note that a subquery is necessary if you want to return every full record in A.

SELECT t1.*
FROM A t1
INNER JOIN
(
    SELECT A.id
    FROM A
    INNER JOIN C
        ON A.id = C.id_a
    GROUP BY A.id
    HAVING COUNT(*) = (SELECT COUNT(*) FROM B)
) t2
    ON t1.id = t2.id

edited Aug 29 '16 at 01:29

answered Aug 29 '16 at 01:20

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 3 · answered Aug 29 '16 at 02:12

Assuming referential integrity enforced by FK constraints, all key columns NOT NULL and a UNIQUE or PK constraint on (id_a, id_b) in C.

If all you need is IDs, only work with table C. Don't waste time joining to A:

SELECT id_a
FROM   C
GROUP  BY 1
HAVING count(*) = (SELECT count(*) FROM B);

If you need columns or whole rows from A, join to it after aggregating and eliminating non-qualifying rows. Should be fastest.

SELECT A.*
FROM  (
   SELECT id_a AS id
   FROM   C
   GROUP  BY 1
   HAVING count(*) = (SELECT count(*) FROM B)
   ) c
JOIN   A USING (id);

kjmerf · Answer 4 · 2016-09-01T19:12:11.980

0

Seems like you really only need table C if what you want is a list of users that have every privilege. One way to this is with CONCAT which looks at the pairing between C.id_a and C.id_b as one string:

SELECT C.id_a
 FROM C
 GROUP BY C.id_a
 HAVING COUNT(DISTINCT CONCAT(C.id_a, C.id_b)) =
 (SELECT COUNT(DISTINCT C.id_b)
  FROM C)

Tested here: http://sqlfiddle.com/#!9/f92a54/3

edited Sep 01 '16 at 19:12

answered Aug 29 '16 at 03:07

kjmerf

4,275
3
21
29

joop · Answer 5 · 2016-08-29T10:58:01.840

0

You don't need to count, you only need to check for (non)existant rows:

SELECT *
FROM A
WHERE NOT EXISTS (
        SELECT * FROM B
        WHERE NOT EXISTS (
                SELECT * FROM C
                WHERE C.id_a = A.id AND C.id_b = B.id
                )
        );

edited Aug 29 '16 at 10:58

answered Aug 29 '16 at 09:05

joop

4,330
1
15
26

SQL Retrieve rows that have all rows from other table

5 Answers5