CODEWITHSUNDEEP
sqlpostgresql
lostdorje
lostdorje

Reputation: 6460

How to return nothing from many-to-many if right side contains something specific?

I have a many-to-many mapped via an ORM. I select from the left table and use 2 left outer joins to pull in the right table.

Everything works fine, but I have a case where if the right table contains a specific value, then the query should return nothing.

Here is a contrived, simplified example:

 people          people_fruits          fruits
id | name     people_id | fruit_id     id | name
---------------------------------------------------
 1 | Tom              1 | 1             1 | apple
 2 | Paul             1 | 2             2 | banana
 3 | Kim              2 | 1             3 | orange
                      2 | 3

A given query might look like:

SELECT p.*, f.*
FROM people AS p
         LEFT OUTER JOIN people_fruits AS pf ON pf.people_id = p.id
         LEFT OUTER JOIN fruits AS f ON pf.fruits_id=f.id
WHERE p.id = 1;

Pretty standard. But let's say I want to pretend people associated with oranges simply don't exist (sorry orange fans). I want the following query to return an empty set:

SELECT p.*, f.*
FROM people AS p
         LEFT OUTER JOIN people_fruits AS pf ON pf.people_id = p.id
         LEFT OUTER JOIN fruits AS f ON pf.fruits_id=f.id
WHERE p.id = 2;

This query should return an empty set.

Queries for Tom and Kim should return results, but queries for Paul should not.

How can such a query be written?

Upvotes: 0

Views: 43

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use NOT EXISTS or NOT IN as following. NOT IN must be used carefully as it needs to handle null condition along with it.

-- NOT EXISTS --
SELECT p.*, f.*
FROM people AS p
LEFT JOIN people_fruits AS pf ON pf.people_id = p.id
LEFT JOIN fruits AS f ON pf.fruits_id = f.id

-- added this where condition with not exists
WHERE NOT EXISTS (SELECT 1
FROM people_fruits PFIN JOIN fruits FIN
ON PFIN.fruits_id = FIN.id
AND FIN.NAME = 'orange'
and PFIN.people_id = p.id);



-- NOT IN --
SELECT p.*, f.*
FROM people AS p
LEFT JOIN people_fruits AS pf ON pf.people_id = p.id
LEFT JOIN fruits AS f ON pf.fruits_id = f.id

-- added this where condition with not IN
WHERE p.id not in (SELECT PFIN.people_id
FROM people_fruits PFIN JOIN fruits FIN
ON PFIN.fruits_id = FIN.id
AND FIN.NAME = 'orange');

Cheers!!

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

One option would be to aggregate by person and assert that he is never associated with oranges:

WITH cte AS (
    SELECT p.id
    FROM people AS p
    LEFT JOIN people_fruits AS pf ON pf.people_id = p.id
    LEFT JOIN fruits AS f ON pf.fruits_id = f.id
    GROUP BY p.id
    HAVING COUNT(*) FILTER (WHERE f.name = 'orange') = 0
)

SELECT p.*, f.*
FROM people AS p
LEFT JOIN people_fruits AS pf ON pf.people_id = p.id
LEFT JOIN fruits AS f ON pf.fruits_id = f.id
WHERE p.id IN (SELECT id FROM cte);

Upvotes: 0

Related Questions