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
– Rogger Fernandes, Commented Aug 29, 2016 at 1:10
a and b dont have a relationship ... yes they do, through table C. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 29, 2016 at 1:15

klin · Accepted Answer · 2016-08-29 02:50:20Z

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.

Tim Biegeleisen · Accepted Answer · 2016-08-29 01:29:46Z

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

Erwin Brandstetter · Accepted Answer · 2016-08-29 02:12:46Z

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);

joop · Accepted Answer · 2016-08-29 10:58:01Z

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 ) );

kjmerf · Accepted Answer · 2016-09-01 19:12:11Z

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

Collectives™ on Stack Overflow

SQL Retrieve rows that have all rows from other table

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Related