PostgreSQL - how to query "result IN ALL OF"?

Question

I am new to PostgreSQL and I have a problem with the following query:

WITH relevant_einsatz AS (
    SELECT einsatz.fahrzeug,einsatz.mannschaft 
    FROM einsatz 
    INNER JOIN bergefahrzeug ON einsatz.fahrzeug = bergefahrzeug.id
),
relevant_mannschaften AS (
    SELECT DISTINCT relevant_einsatz.mannschaft
    FROM relevant_einsatz
    WHERE relevant_einsatz.fahrzeug IN (SELECT id FROM bergefahrzeug)
    )
SELECT mannschaft.id,mannschaft.rufname,person.id,person.nachname 
FROM mannschaft,person,relevant_mannschaften WHERE mannschaft.leiter = person.id AND relevant_mannschaften.mannschaft=mannschaft.id;

This query is working basically - but in "relevant_mannschaften" I am currently selecting each mannschaft, which has been to an relevant_einsatz with at least 1 bergefahrzeug.

Instead of this, I want to select into "relevant_mannschaften" each mannschaft, which has been to an relevant_einsatz WITH EACH from bergefahrzeug.

Does anybody know how to formulate this change?

Answer 1

The information you provide is rather rudimentary. But tuning into my mentalist skills, going out on a limb, I would guess this untangled version of the query does the job much faster:

SELECT m.id, m.rufname, p.id, p.nachname
FROM   person        p 
JOIN   mannschaft    m ON m.leiter = p.id
JOIN (
    SELECT e.mannschaft
    FROM   einsatz       e
    JOIN   bergefahrzeug b ON b.id = e.fahrzeug -- may be redundant
    GROUP  BY e.mannschaft
    HAVING count(DISTINCT e.fahrzeug)
         = (SELECT count(*) FROM bergefahrzeug)
    ) e ON e.mannschaft = m.id

Explain:

• In the subquery e I count how many DISTINCT mountain-vehicles (bergfahrzeug) have been used by a team (mannschaft) in all their deployments (einsatz): count(DISTINCT e.fahrzeug)

  If that number matches the count in table bergfahrzeug: (SELECT count(*) FROM bergefahrzeug) - the team qualifies according to your description.

• The rest of the query just fetches details from matching rows in mannschaft and person.

You don't need this line at all, if there are no other vehicles in play than bergfahrzeuge:

JOIN   bergefahrzeug b ON b.id = e.fahrzeug

Basically, this is a special application of relational division. A lot more on the topic under this related question:
How to filter SQL results in a has-many-through relation

Answer 2

Do not know how to explain it, but here is an example how I solved this problem, just in case somebody has the some question one day.

WITH dfz AS (
    SELECT DISTINCT fahrzeug,mannschaft FROM einsatz WHERE einsatz.fahrzeug IN (SELECT id FROM bergefahrzeug)
), abc AS (
    SELECT DISTINCT mannschaft FROM dfz
), einsatzmannschaften AS (
    SELECT abc.mannschaft FROM abc WHERE (SELECT sum(dfz.fahrzeug) FROM dfz WHERE dfz.mannschaft = abc.mannschaft) = (SELECT sum(bergefahrzeug.id) FROM bergefahrzeug)
)
SELECT mannschaft.id,mannschaft.rufname,person.id,person.nachname
FROM mannschaft,person,einsatzmannschaften WHERE mannschaft.leiter = person.id AND einsatzmannschaften.mannschaft=mannschaft.id;