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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -