Join question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join question

Author

Topic

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-12 : 14:32:53

Hello, If I have this situation

FROM Table1 
INNER JOIN Table2 ON Table1.Ref = Table2.Ref 
LEFT JOIN Table3 ON Table1.Rer = Table3.Ref 
INNER JOIN LookupTable ON Table3.Val1 = LookupTable.Val1 AND LookupTable.Flag1 = 1

I want all values from Table1 (based on Table2) even if there is no match in Table3. I am summing information from Table3 so there is a group by that only uses columns in Table1.

What I am getting is only where there is a match between Table1 and Table3.

Thank you,
djj

djj

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-12 : 14:46:09

Since you didn't post sample data and expected output, it's hard to say, but my guess would be change the INNER JOIN on the LookupTable to a LEFT OUTER JOIN.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-12 : 14:47:23

agreed -
Because you are inner joining LookupTable by a column in Table3 you are essentially turning the left OUTER join into an INNER join. Change the join to LookupTable into an OUTER join.

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-12 : 15:44:51

What I need to do is sum only the Table3 values that match the criteria from the lookup table. With the LEFT OUTER JOIN I get too many values.

Example data
Table1
RefNum Val1
1272989 14397.38
1612428 7709.51
1793655 7700.00
1818997 7983.81

Table3
RefNum TranAmt TranFlag
1272989 100.00 F
1272989 100.00 A
1272989 100.00 F
1272989 100.00 B
...
-- There are 358 records before the flag check
-- There are 292 records after the flag check

LookupTable
TranFlag Ok2Use
F 1
A 0
B 0

What I get with the LEFT OUTER JOIN on the lookup table to transaction table (Table3).

RefNum Val1 TotalTranType1
1272989 14397.38 7672.38
1612428 7709.51 2389.51
1793655 7700 5500
1818997 7983.81 6480

What I would like

RefNum Val1 TotalTranType1
1272989 14397.38 6072.38
1612428 7709.51 2389.51
1793655 7700 3700
1818997 7983.81 2640

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-12 : 16:38:11

It would be easier if your posted sample data matches up with your desired results. Even easier if you provided executable sample code. But - guessing - how about this:


;with Table1 (RefNum, Val1)
as
(
       select 1272989, 14397.38 union all
       select 1612428, 7709.51 union all
       select 1793655, 7700.00 union all
       select 1818997, 7983.81
)
, Table3 (RefNum, TranAmt, TranFlag)
as
(
       select 1272989, 100.00, 'F' union all
       select 1272989, 100.00, 'A' union all
       select 1272989, 100.00, 'F' union all
       select 1272989, 100.00, 'B'
)
, LookupTable (TranFlag, Ok2Use)
as
(
       select 'F', 1 union all
       select 'A', 0 union all
       select 'B', 0
)

select t1.RefNum 
       ,t1.Val1
       ,sum(TranAmt) as TotalTranType1
from   table1 t1
left outer join table3 t3
       on t3.RefNum = t1.refNum
left join LookupTable l
       on l.TranFlag = t3.TranFlag
where  OK2Use is null or Ok2Use = 1
group by t1.RefNum 
       ,t1.Val1

OUTPUT:
RefNum      Val1                                    TotalTranType1
----------- --------------------------------------- -----------------
1793655     7700.00                                 NULL
1612428     7709.51                                 NULL
1818997     7983.81                                 NULL
1272989     14397.38                                200.00

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-14 : 08:35:37

Thanks TG, I will give that a try. (Sorry for not getting back with you, I was out yesterday)

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources