I've a postgres table with data like
| Name | Attendance |
|---|---|
| Jackie | 2 |
| Jade | 5 |
| Xi | 10 |
| Chan | 15 |
In my query I want all present by name, and if name doesn't exist return "null" instead of no row for that particular name
Eg
query where Name in ('Jackie', 'Jade', 'Cha', 'Xi')
Should return
| Name | Attendance |
|---|---|
| Jackie | 2 |
| Jade | 5 |
| NULL | NULL |
| Xi | 10 |
To produce the desired rows, you need to join with a table or set of rows which has all those names.
You can do this by inserting the names into a temp table and joining on that, but in Postgres you can turn an array of names into a set of rows using unnest. Then left join with the table to return a row for every value in the array.
select attendances.*
from
unnest(ARRAY['Jackie','Jade','Cha','Xi']) as names(name)
left join attendances on names.name = attendances.name;
