Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

Top Categories · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

Left Outer Join on same table

Aug 11 2014 12:00AM by Spunny   

HI,

I have a table that gets populated from 3<sup>rd</sup> party system. We don’t have control over it. So, the table has master record (master) and children. Master type is 78 and children’s type is 64. So, it looks like this. In the 3<sup>rd</sup> party system, if Master transaction gets cancelled, it is recorded as type 178. If child is cancelled, then it is 164. Once the child is cancelled and created again using one process then newly created transaction will have 65 as type. Same thing with Master cancelled transaction also. It will be 79. So, to summarize:

Master:                                                                                                                                                                      

Brand New Transaction type = 78

Cancelled Transaction type = 178

Cancelled with creation transaction type = 79

Child:

Brand New Transaction type = 64

Cancelled Transaction type = 164

Cancelled with creation transaction type = 65

I don’t have to bother about master records. I need to focus on only children for my query.

ID

TxnID

Master

Type

TDate

Location

193075

211554

211543

64

20140805

ABC

193076

211555

211543

64

20140805

NBC

193077

211556

211543

64

20140805

ABC

193080

211559

211558

64

20140805

ABC

193081

211562

211561

64

20140805

ABC

193082

211565

211564

64

20140805

CBC

193083

211565

211564

164

20140805

CBC

193084

211566

211564

65

20140805

AZC

--drop table #Transactions

CREATE TABLE #Transactions

(

ID int,

TxnID int,

mstTicket int,

Typecode int,

Tdate datetime,

Location varchar(10)

)

select * from #Transactions

Insert into #Transactions (ID, TxnID, mstTicket,Typecode,Tdate,Location)

Select 193075, 211554,211543,64,'2014-08-05','ABC' UNION ALL

Select 193076, 211555,211543, 64, '2014-08-05', 'NBC' UNION ALL

Select 193077, 211556, 211543, 64, '2014-08-05',                                 'ABC' UNION ALL

Select 193080, 211559, 211558, 64, '2014-08-05',                                 'ABC' UNION ALL

Select 193081, 211562, 211561, 64, '2014-08-05',                                 'ABC' UNION ALL

Select 193082, 211565, 211564, 64, '2014-08-05',                                 'CBC' UNION ALL

Select 193083, 211565, 211564, 164, '2014-08-05',                               'CBC' UNION ALL

Select 193084, 211566, 211564, 65, '2014-08-05',                                 'AZC'

select T.TxnID, T.TypeCode, T.Location, TL.TxnID

From #Transactions T

     Left Outer JOIN #Transactions TL ON TL.TxnID = T.TxnID and TL.TypeCode = 164

select T.TxnID, T.TypeCode, T.Location, TL.TxnID

From #Transactions T

     Left Outer JOIN #Transactions TL ON TL.TxnID = T.TxnID and TL.TypeCode = 164

Where T.typecode in (64, 65)

I need a clarification regarding left Outer Join. 

In the first left outer join query both 64 and 164 both have TL.TxnID populated. Why is that?. What I understand from

left outer join is that ‘Returns all the rows’ from left table and only matching data from right table.

Here, matching row from right table is 211565 and 164 record (id 193083). So, only it should have TxnID populated. But row 211565 and 64 has TL.txnID getting populated (ID 193082).

Why is it? Am I not understanding left out join properly?

Some one in other forum responded as:

By running this query:

select T.TxnID, T.TypeCode, TL.TxnID TLID
From #Transactions T
     Left Outer JOIN #Transactions TL ON TL.TxnID = T.TxnID --and TL.TypeCode = 164
  where T.TxnID = 211565

It results 4 rows where you get same TLID 211565 for each row.

When you join only on TxnID, it results matching rows from LEFT table based on the TxnID.

When you add "And TL.TypeCode = 164" it just filters the RIGHT result set for this typecode (Otherwise it will give you all 4 rows for this TxnID as above).

If you want TLID to be populated only for TypeCode = 164, you have to join them on TypeCode like below:

select T.TxnID, T.TypeCode, T.Location, TL.TxnID TLID
From #Transactions T
Left Outer JOIN #Transactions TL ON TL.TxnID = T.TxnID and TL.TypeCode = T.TypeCode
and TL.TypeCode = 164

But I would like to know why

When "And TL.TypeCode = 164" is added, it just filters the RIGHT result set for this typecode (Otherwise it will give you all 4 rows for this TxnID as above).

If you do this, all TL.TxnID s are null except for 64 and 164 rows. Why is 64 txnID is not null on right result set which has cancellation. This:

Thank You

Spunny

Submitted under: Microsoft SQL Server · TSQL ·  ·  · 


Spunny
143 · 1% · 353

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]