Outer join add non matching rows to each order group?

Hi in my outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?

if OBJECT_ID('tempdb..#left_table') is not null
	drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
	drop table #right_table;
	
create table #left_table
(
	ID int identity(1,1),
	AppName varchar(100),
	KeyName varchar(100),
	AppKey varchar(100)
);

create table #right_table
(
	OrdNO int,
	AppName varchar(100),
	KeyName varchar(100),
	KeyValue varchar(100)
);

insert into #left_table
(
AppName,KeyName,AppKey
)
SELECT 'Random','CurCode','C49C5681-8A' UNION ALL
SELECT 'Random','DCity','F0D4F945-C7' UNION ALL
SELECT 'Random','DSt','57CA5760-3E' UNION ALL
SELECT 'Random','PCity','065C564A-86' UNION ALL
SELECT 'Random','PSt','B7FAC9E9-CA'

insert into #right_table
(
	OrdNO,AppName,KeyName,KeyValue
)
select 1, 'Random', 'cad', 'C49C5681-8A' union all
select 1, 'Random', 'Chicago', 'F0D4F945-C7' union all
select 1, 'Random', 'Illinois', '57CA5760-3E' union all
select 2, 'Random', 'cad', 'C49C5681-8A' union all
select 2,'Random', 'New York', 'F0D4F945-C7' union all
select 2, 'Random', 'New York', '57CA5760-3E'

select * from 
#left_table as a
left join 
#right_table as b
on a.AppKey = b.KeyValue
order by OrdNO

Open in new window

Scarlett72Asked:
Who is Participating?
 
chaauCommented:
If I understood you correctly this query will give the desired result:
;with mandatory_elements as(
	SELECT ID,AppName,KeyName,AppKey from #left_table where Keyname IN('PCity', 'PSt')
	),
mandatory_included as(
	SELECT r.OrdNo, l.ID,l.AppName,l.KeyName,l.AppKey 
	FROM (SELECT DISTINCT OrdNo FROM #right_table) r CROSS JOIN mandatory_elements l)
SELECT m.ID,m.AppName,m.KeyName,m.AppKey,
m.OrdNO,r.AppName,r.KeyName,r.KeyValue
from mandatory_included m LEFT JOIN #right_table r 
ON m.OrdNO = r.OrdNO and m.AppKey = r.KeyValue
UNION ALL 
SELECT a.ID,a.AppName,a.KeyName,a.AppKey,
b.OrdNO,b.AppName,b.KeyName,b.KeyValue
FROM #left_table as a
INNER join 
#right_table as b
on a.AppKey = b.KeyValue
WHERE a.AppKey NOT IN (SELECT AppKey FROM mandatory_elements)
order by OrdNO

Open in new window

Basically, you define the mandatory_elements CTE where you list all the elements you wish to be included into the final select statement. Then you cross-join this CTE with all Orders. This gives you the mandatory_included CTE. Then you need to list the orders from mandatory_included CTE and add the remaining elements using a UNION ALL
0
 
chaauCommented:
It is not really clear what you are asking. Can you provide a desired result
0
 
Scarlett72Author Commented:
Thank you chaau, I will test tomorrow morning and post back
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Scarlett72, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.