Get records where a key occurs alongside all of a set of values-CodePudding

I have two tables table1 and table2. A third table, table3, relates these two tables such that every record in table3 is the primary key, a foreign key to table1, and a foreign key to table2.

I have a selection of table2, and want to find all records in table1 which have a corresponding record in table3 for each of the records selected form table2

Table1
a
b
c

Table2 Selection
1
2
3

Table3

fk1	fk2
a	1
a	2
b	1
b	2
b	3
c	2
c	3
c	4

In this example, I want to find record b but not record a or c

I can make a join with:

select * 
from table3 
inner join table1 
inner join table2 on table3.fk1=table1.id and table3.fk2=table2.id 
where *table2 selection criteria*

But that just gives me everything in one table, I don't know how to require that you match ALL members of the selection, instead of any member

select eveonline_evecharacter.*
    from eveonline_evecharacter
    inner join mechanicus_characterskillmap on eveonline_evecharacter.id = mechanicus_characterskillmap.character_id
    inner join mechanicus_skill on mechanicus_skill.id = mechanicus_characterskillmap.skill_id
    inner join mechanicus_blueprintskill on mechanicus_blueprintskill.skill_id = mechanicus_skill.id
    inner join mechanicus_blueprintoutput on mechanicus_blueprintoutput.blueprint_id=mechanicus_blueprintskill.blueprint_id
    inner join mechanicus_item on mechanicus_item.id = mechanicus_blueprintoutput.item_id
    where mechanicus_item.name = "Small Trimark Armor Pump I"
    group by eveonline_evecharacter.id
    having count (*) = (select count(*) from mechanicus_skill
        inner join mechanicus_blueprintskill on mechanicus_blueprintskill.skill_id = mechanicus_skill.id
        inner join mechanicus_blueprintoutput on mechanicus_blueprintoutput.blueprint_id=mechanicus_blueprintskill.blueprint_id
        inner join mechanicus_item on mechanicus_item.id = mechanicus_blueprintoutput.item_id
        where mechanicus_item.name = "Small Trimark Armor Pump I");

CodePudding user response：

Get the count of the joined rows and compare this with the count of all the rows in table 2.

SELECT t1.*
FROM table1 AS t1
JOIN table3 AS t3 ON t3.fk1 = t1.id
JOIN table2 AS t2 ON t3.fk2 = t2.id
WHERE *some condition on table 2*
GROUP BY t1.id
HAVING COUNT(*) = (
    SELECT COUNT(*) FROM table2 WHERE *some condition on table 2*
)

This assumes that all the IDs in table1 and table2 are unique, and all the pairs are unique. If not, use HAVING COUNT(DISTINCT t1.id) = (SELECT COUNT(DISTINCT id) FROM table2).