Multi Table Join adding columns SQL Server

Question

Table1

ID      FID    DID
1       91     9
2       92     9
3       34     862

Table2

FID      Office Name
9        CompABC
862      CompXYZ

Table3

FID       TotalInvoice
91        850
91        450
91        450
92        450
34        300
34        325

The results from the query I am trying to achieve are like so

9    CompABC 2200
862  CompXYZ 625

I have tried something like

Select SUM(t3.TotalInvoice) as InvoiceTotal
,t2.[Office Name]
from Table2 t2
inner join table1 t1 on t2.FID = t1.DID
inner join table3 t3 on t1.FID = t3.FID

DID are actually entities of Table1 so a full list would include itself.

I am getting improper results here any help would be appreciated.

zigdawgydawg · Accepted Answer · 2013-09-16 22:17:33Z

1

Try this:

select t2.FID, t2.[Office Name], SUM(t3.TotalInvoice) 
from Table2 t2
join Table1 t1 on t2.FID = t1.DID
join Table3 t3 on t1.FID = t3.FID
group by t2.FID, t2.[Office Name]

answered Sep 16, 2013 at 22:17

zigdawgydawg

2,04613 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Derek Over a year ago

sqlfiddle.com/#!3/66912/1/0 - you beat me to it, so I put your query in an sql fiddle.

gurugarzah Over a year ago

Well I have tried several combinations, Thanks for your answer the I am getting different results however if I just Select SUM(t3.TotalInvoice) where FID = 9 is exactly "the amount of times 9 is in the FID field" divided by the Value I get when I run your query.

zigdawgydawg Over a year ago

Hi. I'm not sure what these problem is. Check out the sqlfiddle in the comment Derek left. The query works there. Try to figure out what is different with your code or data.

Collectives™ on Stack Overflow

Multi Table Join adding columns SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related