CODEWITHSUNDEEP
sql
Kevin
Kevin

Reputation: 23

SQL query with muliple tables and results

I have multiple tables that I need to link together. I used joins but I get duplicated results. 

Table1.ID1
Table1.ID2 (Multiple Values)
Table2.Cost linked to Table1.ID2 (Multiple Values)
Table3.Qty linked to Table1.ID2 (Multiple Values)
Table4.AddCost linked to Table1.ID2 (Multiple Values)
Where Table1.ID1 = 'Unique1'

What I have is:

Select 
    Table1.ID1, Table1.ID2, 
    Sum(Table2.Cost), Sum(Table3.QTY), Sum(Table4.AddCost)
From 
    Table1
inner join 
    Table2 on Table2.ID2 = Table1.ID2
Inner Join 
    Table3 on Table3.ID2 = Table2.ID2
Inner Join 
    Table4 on Table4.ID2 = Table2.ID2
Where 
    Table1.ID1 = 'Unique1'
Group by 
    Table1.ID1, Table1.ID2
Order by 
    Table1.ID2

The problem is, Table1.ID2 has multiple values matching Table1.ID1. Table2,3, and 4 are transaction tables and I want to SUM each transaction that matches Table1.ID2.

I was thinking of using a For Each loop, but I hear loops shouldn't be used in SQL.

The end result I'm looking for is this:

'Table1.ID2 'A' | Sum table2 | Sum Table3 | Sum Table4 (Where Table2,3,4 = Table1.ID2 'A'; and where Table1.ID1 = 'Unique1')
'Table1.ID2 'B' | Sum table2 | Sum Table3 | Sum Table4 (Where Table2,3,4 = Table1.ID2 'B'; and where Table1.ID1 = 'Unique1')
'Table1.ID2 'C' | Sum table2 | Sum Table3 | Sum Table4 (Where Table2,3,4 = Table1.ID2 'C'; and where Table1.ID1 = 'Unique1')

Any thoughts?

Thanks,

Kevin

Upvotes: 0

Views: 55

Answers (1)

D Stanley
D Stanley

Reputation: 152566

The problem is that you don't have any linking between tables 2, 3, and 4, so the query is effectively cross-joining them.

It seems like the result you want needs to be done with sub-queries:

Select T1.ID1, T1.ID2, T2.Cost, T3.QTY, T4.AddCost
From Table1 T1
inner join (SELECT ID2, SUM(Cost) Cost FROM Table2 GROUP BY ID2) T2
on T2.ID2 = T1.ID2
Inner Join (SELECT ID2, SUM(QTY) QTY FROM Table3 GROUP BY ID2) T3
on T3.ID2 = T1.ID2
Inner Join (SELECT ID2, SUM(AddCost) AddCost FROM Table4 GROUP BY ID2) T4
on T4.ID2 = T1.ID2
Where T1.ID1 = 'Unique1'
Order by T1.ID2

or by inlining the subqueries:

Select 
    T1.ID1,
    T1.ID2,
    (SELECT SUM(Cost) FROM Table2 WHERE ID2 = T1.ID2) Cost,
    (SELECT SUM(QTY) FROM Table3 WHERE ID2 = T1.ID2) QTY,
    (SELECT SUM(AddCost) FROM Table4 WHERE ID2 = T1.ID2) AddCost
FROM Table1 T1
Where T1.ID1 = 'Unique1'
Order by T1.ID2

Upvotes: 1

Related Questions