'LEFT JOIN with EXTRA INFORMATION
I have 2 tables
table1: households
| Serial_no | Address |
|---|---|
| sn1 | New York |
| sn2 | Maryland |
| sn3 | France |
table2: citizens
| Serial_id | Fullname | Role | household_id |
|---|---|---|---|
| 1 | John | Head | sn1 |
| 2 | Jane | Spouse | sn1 |
| 3 | Johny | Son | sn1 |
| 4 | Mike | Head | sn2 |
I want the output to be like this:
| Serial_no | Address | Total_count | Head |
|---|---|---|---|
| sn1 | New York | 3 | John |
| sn2 | Maryland | 1 | Mike |
| sn3 | France | 0 | null |
I'm stuck here. please help. Thanks in advance!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|