troubleshooting Question

How do I join Transaction Detail and Transaction Payment tables into one table?

Avatar of WestSoft
WestSoftFlag for United States of America asked on
DatabasesMicrosoft SQL Server 2008
2 Comments1 Solution261 ViewsLast Modified:
I need to figure out the SQL to join three tables to produce one resulting merged table.  I have as source tables Header, Detail and Payments.  I'd like to merge them into a table called Merged.  This is basically sales transaction data where each transaction (Header) can have many line items (Detail) and many forms of payment (Payments).  As an oddity, the TotalTax for each transaction is stored in the Header table rather than the Detail table.  

Sample data and my SQL attempts follows:  (I retyped this all so forgive any errors.  I think it accurately represents my challenge.)

Header h
----------
TranId TotalTax
1	1.15
2	2.01
3	0.13
4	5.14
5	1.01

Detail d
----------
ItemId	TranId	ItemAmt
1	1	2.99
2	1	0.55
3	1	4.99
4	2	1.99
5	2	8.49
6	3	1.19
7	4	25.15
8	5	10.99


Payments p
----------
PmtId	TranId	PmtAmt	Type
1	1	9.68	Cash
2	2	10.00	Cash
3	2	2.49	Credit
4	3	1.32	Debit
5	4	30.47	Credit
6	5	6.00	Cash
7	5	6.00	Credit


Merged m
----------
TranId	Total	PmtAmt	Diff
1	9.68	9.68	0.00
2	12.49	12.48	0.01
3	1.32	1.32	0.00
4	30.29	30.47	-0.18
5	12.00	12.00	0.00

-------------------------
PSUEDO OF WHAT I WANT:
-------------------------
Insert the following into Merged
TranId       as h.TranId
Total      as h.TotalTax + [sum of all d.ItemAmt where h.TranId = d.TranId]
Pmts       as [sum of all p.PmtAmt where h.TranId = p.TranId]
Diff       as m.Detail - m.Pmts

------------------------
MY FAILED ATTEMPT:
------------------------
SELECT h.TranId	AS TranId,
	SUM(d.ItemAmt) + SUM(DISTINCT(h.TotalTax)) AS Total,
	SUM(p.PmtAmt) AS PmtAmt
FROM 	Header h,
	Detail d,
	Payments p
WHERE 	h.TranId = d.TranId AND h.TranId = p.TranId 
GROUP BY h.TranId 

I also tried using INNER JOINS but got the same results.  The payments amount ends up being wrong.  I can write two separate SQL statements to correctly get the data I want from (a) just the Detail table and (b) just the Payments table.  But I'm having a hard time getting that into a single statement.  So the following gets what I want but in two tables.  

---------------------------------------------------
WORKS TO GET DATA I WANT BUT IT ENDS UP IN TWO TABLES.
HOW DO I BOIL THIS DOWN TO ONE STATMENT THAT PRODUCES ONE TABLE?
---------------------------------------------------
SELECT 	h.TranId AS TranId,
	SUM(p.PmtAmt) AS PmtAmt
FROM Header h
INNER JOIN Payments p ON p.TranId = h.TranId
GROUP BY h.TranId 
GO

TranId	PmtAmt	
1	9.68	
2	12.48	
3	1.32	
4	30.47	
5	12.00	
 
-------------
SELECT 	h.TranId AS TranId,
	SUM(d.ItemAmt) + SUM(DISTINCT(h.TotalTax)) AS Total
FROM Header h
INNER JOIN Detail d ON d.TranId = h.TranId
GROUP BY h.TranId 
GO

TranId	Total	
1	9.68	
2	12.49	
3	1.32	
4	30.29	
5	12.00	
ASKER CERTIFIED SOLUTION
tigin44

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros