CODEWITHSUNDEEP
mysqlsqlgroup-byleft-join
user1455951
user1455951

Reputation: 41

Combining a LEFT JOIN with GROUP BY, whilst also including NULLs

I'm having trouble combining tables that have a one-to-many mapping using LEFT JOIN and GROUP BY.

I have the following table with a unique ID (in the illustrative example this is house_number)

Houses:

|house_number| bedrooms|
|0           | 4       |
|1           | 3       |
|2           | 1       |

And I want to LEFT JOIN with a second table USING the unique ID, where the second table may or may not have multiple entries per unique ID. E.g,

Occupants:

| house_number | occupant_id | type    |
| 0            | 3           | 19      |
| 0            | 1           | 20      |
| 0            | 2           | 21      |
| 2            | 7           | 20      |

Now what I want to achieve is exactly ONE entry per house number, but giving a preference in the LEFT JOIN to occupants with a type of 20, whilst also keeping those houses which do not have any occupants listed, e.g,

|house_number| bedrooms| occupant_id | type    |
|0           | 4       | 1           | 20      |
|1           | 3       | null        | null    |
|2           | 1       | 7           | 20      |

I can use a GROUP BY to achieve only one entry per house, however, I need to ensure that the occupant row returned with it (if it exists) has type = 20.

If I simply use a WHERE (type = 20), then I wouldn't get an entry returned for house_number = 1.

How would I achieve this final table?

Upvotes: 1

Views: 619

Answers (2)

wildplasser
wildplasser

Reputation: 44250

SELECT h.house_number,h.bedrooms
        , o.occupant_id,o.ztype
FROM houses h
LEFT JOIN occupants o ON h.house_number = o.house_number
        AND o.ztype =20
        ;

BTW I had to replace "type" by "ztype" because type is a reserved word in Postgres.

Upvotes: 2

Csaba Benko
Csaba Benko

Reputation: 1161

What about trying WHERE (type = 20 OR type is null) condition instead?

Upvotes: 2

Related Questions