PostgreSQL Return Id from t1 where none of the inner join rows from t2 match second condition-CodePudding

t1 (Properties)

| Id |  Name  |
| -  | ------ |
| 31 | Garage |
| 32 | Depot  |  
| 33 | Store  |

t2 (Addresses)

| Id | Prop_Id | Primary |
| -  | ------- | ------- |
| 1  | 31      | true    |
| 2  | 31      | false   |   
| 3  | 32      | false   |   
| 4  | 32      | false   |

I'm trying to retrieve the Id of a Property(t1) that has at least one match in Address(t2) based on t1.id = t2.prop_id AND all the matches must have t2.primary = false Based on my table data, I would expect a query to return only the Id 32 from t1. But all the queries I'm trying return id 32 but 31 as well which is not correct.

SELECT t1.Id
FROM properties t1
    INNER JOIN addresses t2 ON t1.Id = t2.Prop_Id
WHERE t2.Is_Primary = false

CodePudding user response：

WITH test AS (
    SELECT t1.id, bool_or(t2.primary) as is_primary
    FROM properties t1
    INNER JOIN addresses t2 ON t1.id = t2.prop_id
    GROUP BY t1.id
)
SELECT test.id FROM test where is_primary IS FALSE

CodePudding user response：

Every time I hear "where at least one..." to me that sounds like a semi-join (exists clause).

Here is an example with your query and dataset above:

SELECT t1.Id
FROM
  properties t1
  JOIN addresses t2 ON t1.Id = t2.Prop_Id
WHERE
  t2.Is_Primary = false and
  exists (
    select null
    from addresses a2
    where t1.id = a2.prop_id and a2.is_primary
  )

Most of your query is intact, I just added the exists at the bottom. These are wildly efficient because they look for a match, not all matches.