LEFT OUTER JOIN IN TEMPORARY TABLE

Question

Post reply

LEFT OUTER JOIN IN TEMPORARY TABLE

royalhishighness

SSC Veteran

Points: 213
More actions
September 10, 2010 at 1:07 pm

#100739

Dear All,
I am having 2 temporary tables, when I am trying to select values from both the tables I am getting NULL value from second table.
1st table structure:
create table #totreject(pbank varchar(50), reject int)
data from 1st table
002 100
003 149
005 5
007 5
010 3
011 3
012 30
013 27
014 1
015 11
016 11
017 15
018 8
019 2
020 4
022 15
023 4
024 53
025 6
026 51
027 3
028 52
029 4
030 1
039 3
052 1
064 10
069 3
072 1
111 13
190 5
211 95
229 72
234 24
240 130
259 16
348 3
485 15
514 7
517 29
532 2
646 40
2nd table structure is as follows:
create table #totpresent(pbank varchar(50), totItem int)
data from 2nd table
2 1537
3 2917
5 82
7 102
10 111
11 91
12 774
13 343
14 41
15 228
16 259
17 430
18 58
19 87
20 106
22 415
23 104
24 897
25 87
26 502
27 44
28 618
29 97
30 39
39 294
49 19
52 40
64 421
69 41
72 47
111 137
190 30
211 2244
229 1589
234 317
240 2791
259 472
348 112
485 332
514 191
517 124
532 31
646 18
701 20
join query:
select #totpresent.pbank, #totpresent.totitem, reject
from #totpresent left outer join #totreject
on #totpresent.pbank = #totreject.pbank
order by cast(#totpresent.pbank as int)
result
2 1537 NULL
3 2917 NULL
5 82 NULL
7 102 NULL
10 111 NULL
11 91 NULL
12 774 NULL
13 343 NULL
14 41 NULL
15 228 NULL
16 259 NULL
17 430 NULL
18 58 NULL
19 87 NULL
20 106 NULL
22 415 NULL
23 104 NULL
24 897 NULL
25 87 NULL
26 502 NULL
27 44 NULL
28 618 NULL
29 97 NULL
30 39 NULL
39 294 NULL
49 19 NULL
52 40 NULL
64 421 NULL
69 41 NULL
72 47 NULL
111 137 13
190 30 5
211 2244 95
229 1589 72
234 317 24
240 2791 130
259 472 16
348 112 3
485 332 15
514 191 7
517 124 29
532 31 2
646 18 40
701 20 NULL
Please help me, how to resolve this problem.
Thanks in advance
Pradeep

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

data format nazi
no solution for you!

i guess i'm feeling generous;

I didn't even bother looking at the request, as the lack of consumable data was as far as i cared to take this.

here is readily consumable data for other posters who want to help;

you should post the data in exactly this manner so people can help you...

create table #totreject(pbank varchar(50), reject int)

INSERT INTO #totreject

SELECT '002','100' UNION ALL SELECT '003','149' UNION ALL

SELECT '005','5' UNION ALL SELECT '007','5' UNION ALL

SELECT '010','3' UNION ALL SELECT '011','3' UNION ALL

SELECT '012','30' UNION ALL SELECT '013','27' UNION ALL

SELECT '014','1' UNION ALL SELECT '015','11' UNION ALL

SELECT '016','11' UNION ALL SELECT '017','15' UNION ALL

SELECT '018','8' UNION ALL SELECT '019','2' UNION ALL

SELECT '020','4' UNION ALL SELECT '022','15' UNION ALL

SELECT '023','4' UNION ALL SELECT '024','53' UNION ALL

SELECT '025','6' UNION ALL SELECT '026','51' UNION ALL

SELECT '027','3' UNION ALL SELECT '028','52' UNION ALL

SELECT '029','4' UNION ALL SELECT '030','1' UNION ALL

SELECT '039','3' UNION ALL SELECT '052','1' UNION ALL

SELECT '064','10' UNION ALL SELECT '069','3' UNION ALL

SELECT '072','1' UNION ALL SELECT '111','13' UNION ALL

SELECT '190','5' UNION ALL SELECT '211','95' UNION ALL

SELECT '229','72' UNION ALL SELECT '234','24' UNION ALL

SELECT '240','130' UNION ALL SELECT '259','16' UNION ALL

SELECT '348','3' UNION ALL SELECT '485','15' UNION ALL

SELECT '514','7' UNION ALL SELECT '517','29' UNION ALL

SELECT '532','2' UNION ALL SELECT '646','40'

create table #totpresent(pbank varchar(50), totItem int)

insert into #totpresent

SELECT '2','1537' UNION ALL SELECT '3','2917' UNION ALL

SELECT '5','82' UNION ALL SELECT '7','102' UNION ALL

SELECT '10','111' UNION ALL SELECT '11','91' UNION ALL

SELECT '12','774' UNION ALL SELECT '13','343' UNION ALL

SELECT '14','41' UNION ALL SELECT '15','228' UNION ALL

SELECT '16','259' UNION ALL SELECT '17','430' UNION ALL

SELECT '18','58' UNION ALL SELECT '19','87' UNION ALL

SELECT '20','106' UNION ALL SELECT '22','415' UNION ALL

SELECT '23','104' UNION ALL SELECT '24','897' UNION ALL

SELECT '25','87' UNION ALL SELECT '26','502' UNION ALL

SELECT '27','44' UNION ALL SELECT '28','618' UNION ALL

SELECT '29','97' UNION ALL SELECT '30','39' UNION ALL

SELECT '39','294' UNION ALL SELECT '49','19' UNION ALL

SELECT '52','40' UNION ALL SELECT '64','421' UNION ALL

SELECT '69','41' UNION ALL SELECT '72','47' UNION ALL

SELECT '111','137' UNION ALL SELECT '190','30' UNION ALL

SELECT '211','2244' UNION ALL SELECT '229','1589' UNION ALL

SELECT '234','317' UNION ALL SELECT '240','2791' UNION ALL

SELECT '259','472' UNION ALL SELECT '348','112' UNION ALL

SELECT '485','332' UNION ALL SELECT '514','191' UNION ALL

SELECT '517','124' UNION ALL SELECT '532','31' UNION ALL

SELECT '646','18' UNION ALL SELECT '701','20'

select #totpresent.pbank, #totpresent.totitem, reject

from #totpresent left outer join #totreject

on #totpresent.pbank = #totreject.pbank

order by cast(#totpresent.pbank as int)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

If all you want are rows that match, change the outer join to an inner join.

And then again, I might be wrong ...
David Webb

sanmatrix SSC Veteran Points: 250 More actions · Answer 3

Well the result is right. Just give a closure look. Look at the first row in the result 2, 1537, null. 2, 1537 is from otpresent. There is no corresponding row in otreject for 2. Since being outer join it will return null for the third column in the select list.

If you are saying that '002' from otreject is same as '2' from otpresent then you must understand that the columns are of varchar datatype therefore '002' is different from '2'.

Jeff Moden SSC Guru Points: 1004256 More actions · Answer 4

sanmatrix (9/14/2010)
Well the result is right. Just give a closure look. Look at the first row in the result 2, 1537, null. 2, 1537 is from otpresent. There is no corresponding row in otreject for 2. Since being outer join it will return null for the third column in the select list.
If you are saying that '002' from otreject is same as '2' from otpresent then you must understand that the columns are of varchar datatype therefore '002' is different from '2'.

Spot on... the devil's in the data on this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)