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.