SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 11/12/2013 :  14:32:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 11/12/2013 :  14:46:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/12/2013 :  14:47:23  Show Profile  Reply with Quote
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

USA
283 Posts

Posted - 11/12/2013 :  15:44:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 11/12/2013 :  16:38:11  Show Profile  Reply with Quote
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

USA
283 Posts

Posted - 11/14/2013 :  08:35:37  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000