Left outer join not showing unmatched pairs Oracle SQL

user_14589299 0
Viewed 30

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?

oracle sql
1 Answers

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;
user_22707620
Related