troubleshooting Question

"Join" Question

Avatar of tomfogarty
tomfogartyFlag for Ireland asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008
26 Comments2 Solutions473 ViewsLast Modified:

I have a query which is giving me a bit of trouble. I have used derived tables to put together the correct aggregates for a invoicing report. I'm now trying to break it down by a column for product analysis.

Essentially, I've got two derived tables returning the data I need but when they are joined it is duplicating some rows.
ID1, ID2, Value1, Value2
1, 1, 50, 60
2, 2, 600, 55
3, 2, 555, 899

ID1, ID2, Value3
1, 1, 400
1, 3, 2000

Value 1 and Value 2 are totals taken from an invoice detail table. Value 3 is taken from a delivery table which tracks what has been delivered of the products.

ID1 would basically be sales IDs and ID2 would be product IDs.

To tie these together in the main query I'm using a join on ID1 but I also need to show the FK value for ID2 so I have to join that as well. I think this may be where the problem is.

When they come together I'm getting duplicates on Value 1 and Value 2 and Value 3.

Any ideas?
Join our community to see this answer!
Unlock 2 Answers and 26 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 2 Answers and 26 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