March 30, 2015 at 11:50 am
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
March 30, 2015 at 7:08 pm
Perhaps like this?
WITH AllCombos AS
(
SELECT ID, KeyName, OrdNo
FROM #left_table
CROSS JOIN
(
SELECT OrdNo
FROM #right_table
GROUP BY OrdNo
) b
)
SELECT *
FROM AllCombos a
LEFT JOIN #left_table b ON a.ID = b.ID
LEFT JOIN #right_table c ON b.AppKey = c.KeyValue AND a.OrdNo = c.OrdNo;
You may need to group the left table of the CROSS JOIN, depending on exactly what's in #left_table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 31, 2015 at 8:27 am
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply