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
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
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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It is not really clear what you are asking. Can you provide a desired result
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,
from mandatory_included m LEFT JOIN #right_table r 
ON m.OrdNO = r.OrdNO and m.AppKey = r.KeyValue
SELECT a.ID,a.AppName,a.KeyName,a.AppKey,
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scarlett72Author Commented:
Thank you chaau, I will test tomorrow morning and post back
Vitor MontalvãoMSSQL Senior EngineerCommented:
Scarlett72, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.