CODEWITHSUNDEEP
sqljoin
RememberME
RememberME

Reputation: 2092

sql query join where none of the many meet a condition

I have 3 tables. On the first table, there are multiple entries for each project. The second is basically a mapping table. It's more complicated than this, but for this example I've simplified. There's a simple condition I'm checking for on table 2. On the third table, each entry has a flag that's set to true or false. I want to return rows on the first table where all matching rows on the third table are false. In the example below, the result would return project A b/c all of Jane and Fred's rows in table 3 are false, but none of the other's since every other project has at least one true entry in table 3. 

 Project  | Client           name   | id            id  | active
  ---------------           ----------------       ---------------
    A     |  Jane            John   |  1            1   | false
    A     |  Fred            Jane   |  2            1   | true
    B     |  Mary            Fred   |  3            2   | false
    B     |  Jane            Mary   |  4            2   | false
    C     |  John                                   3   | false
    C     |  Jane                                   3   | false
    D     |  Jane                                   4   | true
    D     |  Mary                                   4   | false
    D     |  John
    D     |  Fred

Upvotes: 0

Views: 415

Answers (4)

dmikam
dmikam

Reputation: 1082

You have to do the select from the first table with two joins and a simple WHERE condition:

SELECT
    res.Project
FROM
    (SELECT
        table1.Project,
        BOOL_OR(res) as active
    FROM
        table1
        JOIN table2 ON table2.name=table1.Client
        JOIN table3 ON table3.id=table2.id
    GROUP BY table1.Project
    ) as res
WHERE
    res.active=FALSE

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

A fairly straight forward JOIN with HAVING should give you the results you want;

SELECT t1.project, t1.client
FROM table1 t1
JOIN table2 t2 ON t1.client = t2.name
JOIN table3 t3 ON t2.id = t3.id
GROUP BY t1.project, t1.client
HAVING NOT MAX(t3.active)

An SQLfiddle to test with.

This basically just does a straight forward join of all tables, groups the results by client and project. It then uses NOT MAX(t3.active) to check that all booleans in the group are false.

This version chooses to not return clients/projects that don't have any active flags to check.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The following should do what you want:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2 join
                       table3 t3
                       on t2.id = t3.id
                  where t2.name = t1.name and t3.active <> false
                 );

There is some ambiguity about what to do when one of the joins fails (this condition is not present in the sample data). This will return the row, because all matching rows in the third table are false, even in that case.

Upvotes: 1

Mihail Shishkov
Mihail Shishkov

Reputation: 15797

SELECT t1.* FROM t1 
INNER JOIN t2 ON t1.Client = t2.name
WHERE t2.id IN (
SELECT id FROM t3 
GROUP BY id, active
HAVING SUM(CASE active WHEN false THEN 1 ELSE 0 END) = COUNT(1)
)

Upvotes: 0

Related Questions