Left Join Issue

Question

Post reply

Left Join Issue

pramodyg

Grasshopper

Points: 16
More actions
March 6, 2012 at 11:27 pm

#252050

Hi,
There is a query below… I need the last 2 select queries to return the same data.
BEGIN TRY DROP TABLE #AUM_Raw_Upload1 END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP TABLE #temp1 END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP TABLE #temp2 END TRY BEGIN CATCH END CATCH;
create table #temp1
(a1 int,a2 int)
create table #temp2
(a3 int,
a4 int,
aum int)
insert into #temp1 values (1,4)
insert into #temp1 values (2,5)
insert into #temp1 values (3,null)
insert into #temp2 values (1,2,100)
insert into #temp2 values (3,1,200)
insert into #temp2 values (2,3,100)
insert into #temp2 values (2,10,500)
insert into #temp2 values (3,5,200)
insert into #temp2 values (10,29,100)
SELECT CASE WHEN a3 = a1 THEN a1 ELSE NULL END AS CHILDACCOUNT1
, CASE WHEN a3 = a2 THEN a2 ELSE NULL END AS PARENTACCOUNT1
, CASE WHEN a4 = a1 THEN a1 ELSE NULL END AS CHILDACCOUNT2
, CASE WHEN a4 = a2 THEN a2 ELSE NULL END AS PARENTACCOUNT2
, am.*
INTO #AUM_Raw_Upload1
FROM #temp2 am
LEFT
JOIN #temp1 at
ON a1 IN (a3, a4);
select count(*), sum(aum) from #AUM_Raw_Upload1 --- 1
select count(*), sum(aum) from #temp2 --- 2
Can anyone please help me in retaining the exact number of rows as the #AUM_Raw_Upload1 in the result set?
Thanks in Advance.
Pramod

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

That's a very strange join condition. Why is that?

Do you mind sharing some logic behind these queries?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Not sure if we can. Just running your code the two tables have different data.

#temp2:

a3a4aum

12100

31200

23100

210500

35200

1029100

#AUM_Raw_Upload1:

CHILDACCOUNT1PARENTACCOUNT1CHILDACCOUNT2PARENTACCOUNT2a3a4aum

1 NULL NULL NULL 12100

NULL NULL 2 NULL 12100

NULL NULL 1 NULL 31200

3 NULL NULL NULL 31200

2 NULL NULL NULL 23100

NULL NULL 3 NULL 23100

2 NULL NULL NULL 210500

3 NULL NULL NULL 35 200

NULL NULL NULL NULL 1029100

Looking at the raw data, what are we missing?

ard5karthick SSCommitted Points: 1603 More actions · Answer 3

Can You Explain Your requirement more...

What is the purpose of that Complex Case Statement?

pramodyg Grasshopper Points: 16 More actions · Answer 4

The case statements are for finding the Parent and the Child account. It is not sure that which of a3 and a4 will have parent account and child account.

The table temp1 is my account table, temp2 is the raw data from finance which has money linked to the account.

I need to find the parent and child by joining temp2 to temp1. Since the job is only to find the parent and child, in the final table, i need only rows which are in the finance table (temp2) with some extra columns.

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 5

BEGIN TRY DROP TABLE #AUM_Raw_Upload1 END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP TABLE #temp1 END TRY BEGIN CATCH END CATCH;

BEGIN TRY DROP TABLE #temp2 END TRY BEGIN CATCH END CATCH;

create table #temp1

(a1 int,a2 int)

create table #temp2

(a3 int,

a4 int,

aum int)

insert into #temp1 values (1,4)

insert into #temp1 values (2,5)

insert into #temp1 values (3,null)

insert into #temp2 values (1,2,100)

insert into #temp2 values (3,1,200)

insert into #temp2 values (2,3,100)

insert into #temp2 values (2,10,500)

insert into #temp2 values (3,5,200)

insert into #temp2 values (10,29,100)

SELECT ChildAccount1.a1 AS CHILDACCOUNT1

, ParentAccount1.a2 AS PARENTACCOUNT1

, ChildAccount2.a1 AS CHILDACCOUNT2

, ParentAccount2.a2 AS PARENTACCOUNT2

, am.*

INTO #AUM_Raw_Upload1

FROM #temp2 am

LEFT JOIN #temp1 ChildAccount1 ON ChildAccount1.a1 = a3

LEFT JOIN #temp1 ParentAccount1 ON ParentAccount1.a2 = a3

LEFT JOIN #temp1 ChildAccount2 ON ChildAccount2.a1 = a4

LEFT JOIN #temp1 ParentAccount2 ON ParentAccount2.a2 = a4

;

select count(*), sum(aum) from #AUM_Raw_Upload1 --- 1

select count(*), sum(aum) from #temp2 --- 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Not very representable setup. May be if you supply exact result you are expecting from your setup, it will be easeier to answer...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ard5karthick SSCommitted Points: 1603 More actions · Answer 7

Can you Paste the Exact Result that you are expecting...?