I have a table like this, let's call it main
| location | item | price |
|---|---|---|
| l1 | item1 | 3.00 |
| l1 | item2 | 1.00 |
| l2 | item1 | 3.00 |
| l2 | item2 | 1.50 |
| l2 | item3 | 2.00 |
| l3 | item4 | 5.00 |
| l3 | item5 | 5.00 |
and a separate table, call it items
| items |
|---|
| item 1 |
| item 2 |
| item 3 |
| item 5 |
| item 4 |
| item 5 |
| item 6 |
and am currently trying to join the two together with this
with main as (subquery to create main),
items as (select distinct items from main)
select i.items, m.*
from items i left join main m
on i.items = m.items
order by m.location, i.items
and my desired result is
| i.items | location | item | price |
|---|---|---|---|
| item1 | l1 | item1 | 3.00 |
| item2 | l1 | item2 | 1.00 |
| item3 | l1 | null | null |
| item4 | l1 | null | null |
| item5 | l1 | null | null |
| item6 | l1 | null | null |
| item1 | l2 | item1 | 3.00 |
| item2 | l2 | item2 | 1.50 |
| item3 | l2 | item3 | 2.00 |
| item4 | l2 | null | null |
| item5 | l2 | null | null |
| item6 | l2 | null | null |
| item1 | l3 | null | null |
| item2 | l3 | null | null |
| item3 | l3 | null | null |
| item4 | l3 | item4 | 5.00 |
| item5 | l3 | item5 | 5.00 |
| item6 | l3 | null | null |
However, it ends up just looking like the main table but sorted, with none of the unmatched items being shown. Am I doing something wrong?
CodePudding user response：
It seems you want one result row per item and location whether or not that pair has entries in the main table. So first generate these rows with a cross join. Only then outer join your original data.
with main as (<subquery to create main>),
items as (select distinct item from main),
locations as (select distinct location from main)
select i.item, l.location, m.price
from items i
cross join locations l
left join main m on m.item = i.item and m.location = l.location
order by i.item, l.location;