"Join" Question

If you're just trying to eliminate the duplicate values in an otherwise working query, just add DISTINCT to the start of the SELECT column list:

SELECT DISTINCT ID1, ..., Value1, Value2, Value3
FROM ...
...

Hi Tom,

What do you want your final result set to look like? You're correct that when you join both the sales ID AND the product ID you will get duplicate records. Are you looking for an aggregate?

Rgds,

Koen

This is the query. Not sure where I would put a distinct other than in the first select clause as that doesn't make a difference.

SELECT 
dbo.SalesProposal.SalesProposalID, 
dbo.Product.Product,
ISNULL(ProductBreakdown.InvoicedQuantity, 0) AS InvoicedQuantity,
ISNULL(ProductBreakdown.InvoicedAmount, 0) AS InvoicedAmount,
ISNULL(DispatchDerived.DeliveredQuantity, 0) AS DeliveredQuantity
FROM dbo.SalesProposal
INNER JOIN dbo.SalesProposalStatus ON dbo.SalesProposal.StatusID = dbo.SalesProposalStatus.SalesProposalStatusID AND dbo.SalesProposalStatus.SalesProposalStatus = 'Sold'
LEFT OUTER JOIN
(SELECT dbo.Dispatch.SalesProposalID, dbo.Dispatch.ProductID, ISNULL(SUM(dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor), 0) AS DeliveredQuantity
FROM dbo.Dispatch GROUP BY dbo.Dispatch.SalesProposalID, dbo.Dispatch.ProductID) AS DispatchDerived ON DispatchDerived.SalesProposalID = dbo.SalesProposal.SalesProposalID
LEFT OUTER JOIN 
(SELECT dbo.Invoice.SalesProposalID, InvoiceItemDerived.ProductID, ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) As InvoicedQuantity, ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0) As InvoicedAmount
FROM dbo.Invoice LEFT OUTER JOIN 
(SELECT dbo.InvoiceItem.InvoiceID, dbo.InvoiceItem.ProductID, ISNULL(SUM(dbo.InvoiceItem.Quantity), 0) AS InvoicedQuantity, ISNULL(SUM(dbo.InvoiceItem.Quantity * dbo.InvoiceItem.Price), 0) AS InvoicedAmount
FROM dbo.InvoiceItem GROUP BY dbo.InvoiceItem.InvoiceID, InvoiceItem.ProductID) AS InvoiceItemDerived on Invoice.InvoiceID = InvoiceItemDerived.InvoiceID GROUP BY dbo.Invoice.SalesProposalID, InvoiceItemDerived.ProductID) AS ProductBreakdown ON dbo.SalesProposal.SalesProposalID = ProductBreakdown.SalesProposalID
INNER JOIN dbo.Product ON dbo.Product.ProductID = DispatchDerived.ProductID OR dbo.Product.ProductID = ProductBreakdown.ProductID

Select allOpen in new window

A salesproposal is basically a lot to be auctioned. PK is salesproposalID.
Invoice is obvious. Joined to salesproposal table with a FK salesproposalID. PK is invoiceid.
InvoiceItem contains the line items for invoices. Joined to Invoice using Fk invoiceid
Dispatch logs all deliveries of products. It is joined to salesproposal with Fk salesproposalid. PK is dispatchid.
Products (productids) are stored in each invoiceitem and dispatch. Product information is stored in the product table.

Hope this helps with deliberations.

I fyou take the sample data I gave:
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

ID1 being salesproposal ID and ID2 being product ID.

What I want it to look like is:
ID1, ID2, Value1, Value2, Value3
1,1,50,60,400
1,3,0,0,2000
2,2,600,55,0
3,2,555,899,0

It's a report on invoiced vs delivered. Value 2 isn't actually too important. Value 1 and Value 3 (invoiced and delivered quantitites are the most important).

Does this help?

Hi Tom,

Is it possible that the duplicate records are the result of the OR statement in the last inner join? What if you only use 1 of those (just dbo.Product.ProductID = DispatchDerived.ProductID for instance) do you still get the double records?

Rgds,

Koen

If I'm right, you can add the link to products in your subqueries and derive the product descriptions before grouping everything together. That should prevent the duplicates.

Hi Koen,

Sample result sets below. I'm using GUIDs for IDs. The first set is for the query I just gave to you. Second is without the dbo.Product.ProductID = DispatchDerived.ProductID part of the last join and the third is without the dbo.Product.ProductID = ProductBreakdown.ProductID part of the last join.


1BEA66B3-E3F2-4535-9191-1F20EF3225D5      Through & Through      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      Through & Through      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      Pallet      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      Pallet      62.57      2252.520000      2000.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      Energy Wood      62.57      2252.520000      2000.0000



1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      2000.0000
1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000



1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      62.57      2252.520000      2000.0000


Tom.

Correction - the first set of results should look like this:

1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      2000.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      62.57      2252.520000      2000.0000

Hi Kvwielink,

I tried that but the issue I had was when using information from the product table in the main select clause, how do you join it to the 2 derived tables because you essentially have 2 sets of data containing product information.

Tom.

You have LEFT JOIN to DispatchDerived and InvoiceItemDerived FROM SalesProposal but INNER JOIN to Product. But there is no join condition between Product and SalesProposal. Hence your LEFT JOINs work as INNER JOIN. So I changed the LEFT JOINs to INNER JOINs in the below proposed code. You can change based on your data.
Also, I think you need a UNION instead of two LEFT JOINs. give a try and see the result.

;WITH cte 
     AS (SELECT * 
           FROM dbo.SalesProposal SP 
                INNER JOIN dbo.SalesProposalStatus SPS 
                        ON SP.StatusID = SPS.SalesProposalStatusID 
                           AND SPS.SalesProposalStatus = 'Sold'), 
     dispatchderived 
     AS (SELECT D.SalesProposalID, 
                D.ProductID, 
                ISNULL(SUM(D.Weight * D.ConversionFactor), 0) AS DeliveredQuantity 
           FROM dbo.Dispatch D 
          WHERE EXISTS (SELECT 1 
                          FROM dbo.Product P 
                         WHERE D.ProductID = P.ProductID) 
          GROUP BY D.SalesProposalID, 
                   D.ProductID), 
     productbreakdown 
     AS (SELECT I.SalesProposalID, 
                InvoiceItemDerived.ProductID, 
                ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) AS InvoicedQuantity, 
                ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0)   AS InvoicedAmount 
           FROM dbo.Invoice I 
                LEFT OUTER JOIN (SELECT II.InvoiceID, 
                                        II.ProductID, 
                                        ISNULL(SUM(II.Quantity), 0)            AS InvoicedQuantity,
                                        ISNULL(SUM(II.Quantity * II.Price), 0) AS InvoicedAmount
                                   FROM dbo.InvoiceItem II 
                                  GROUP BY II.InvoiceID, 
                                           II.ProductID) AS InvoiceItemDerived 
                             ON I.InvoiceID = InvoiceItemDerived.InvoiceID 
          WHERE EXISTS (SELECT 1 
                          FROM dbo.Product P 
                         WHERE D.productid = InvoiceItemDerived.ProductID) 
          GROUP BY I.SalesProposalID, 
                   InvoiceItemDerived.ProductID) SELECT c1.SalesProposalID, 
       PB.ProductID, 
       ISNULL(PB.InvoicedQuantity, 0) AS InvoicedQuantity, 
       ISNULL(PB.InvoicedAmount, 0)   AS InvoicedAmount, 
       0                              DeliveredQuantity 
  FROM cte c1 
       JOIN productbreakdown PB 
         ON c1.SalesProposalID = PB.SalesProposalID 
UNION ALL 
SELECT c1.SalesProposalID, 
       DD.ProductID, 
       0                               InvoicedQuantity, 
       0                               InvoicedAmount, 
       ISNULL(DD.DeliveredQuantity, 0) AS DeliveredQuantity 
  FROM cte c1 
       JOIN dispatchderived DD 
         ON c1.SalesProposalID = DD.SalesProposalID 

Select allOpen in new window

select coalesce(t1.id1,t2.id1) id1
      ,coalesce(t1.id2,t2.id2) id2
      ,coalesce(t1.value1,0) value1
      ,coalesce(t1.value2,0) value2
      ,coalesce(t2.value3,0) value3
from t1 full outer join t2
on t1.id1 = t2.id1
and t1.id2 = t2.id2
order by 1,2;
Replace t1 and t2 with your derived tables.

Hi Sharath,

Getting an error on this line:
 

         WHERE EXISTS (SELECT 1 
                          FROM dbo.Product P 
                         WHERE D.productid = InvoiceItemDerived.ProductID) 

Select allOpen in new window

The multi-part identifier "D.productid" could not be bound.

Awkingoo - I've done exactly as you say - replaced the t1 and t2 values with the derived tables but not go - getting a lot of syntacical errors.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'id1'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'full'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'on'.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '.'.

Sorry.

I would suggest you adpot the full outer join plus use of coalesce as proposed by by: awking00 (ID: 39018988)

any inner or left joins will require that one derived table as the 'master' whereas the full outer join won't, it will ensure all rows are included. The use coalesce then simplifies the columns to just those you expect in the final result.

I would suggest you adopt the full outer join plus use of coalesce as proposed by awking00 (ID: 39018988)
[sorry for the repeat, but had to correct my awful typo!]

Hi Sharath,

I'm still getting duplicates on one of the items.
SalesProposalID      ProductID      InvoicedQuantity      InvoicedAmount      DeliveredQuantity
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      0.0000
1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      0.00      0.000000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      0.00      0.000000      2000.0000

There is a sales proposal which has rows with more than 1 product.

Hi PortletPaul,

I'm trying to rework my query using what awking suggested but still getting errors. Bearing in mind what you're saying but trying to get it to work.

Thanks.

FYI, the data should look like this:
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.52      155.727
1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000      171000      0
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499      27195.5      0
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      0      0      2000

Just did a bit of editing in Excel to demonstrate it.

Tom.

Tom, can you post your most current code here? This will save much time in the long run.
if possible use a code block (so there are lines numbers to refer to) or attach as a text file.

As best as I can tell it isn't quite as simple as the full outer join example given, not all fields need coalesce as some are exclusive to the 2 derived tables and there's 'product' which isn't in either. This is what I came up with in case its helpful:

SELECT
  derived.SalesProposalID
, dbo.Product.Product
, derived.ProductID
, derived.InvoicedQuantity
, derived.InvoicedAmount
, derived.DeliveredQuantity
FROM (
            SELECT
               coalesce(DispatchDerived.SalesProposalID , ProductBreakdown.SalesProposalID) as SalesProposalID
             , coalesce(DispatchDerived.ProductID, ProductBreakdown.ProductID) as ProductID
             , ISNULL(ProductBreakdown.InvoicedQuantity, 0) AS InvoicedQuantity
             , ISNULL(ProductBreakdown.InvoicedAmount, 0) AS InvoicedAmount
             , DispatchDerived.DeliveredQuantity
            FROM (
                     SELECT dbo.Dispatch.SalesProposalID
                      , dbo.Dispatch.ProductID
                      , ISNULL(SUM(dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor), 0) AS DeliveredQuantity
                     FROM dbo.Dispatch
                     GROUP BY dbo.Dispatch.SalesProposalID
                      , dbo.Dispatch.ProductID
                  ) AS DispatchDerived
            FULL OUTER JOIN (
                     SELECT dbo.Invoice.SalesProposalID
                      , InvoiceItemDerived.ProductID
                      , ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) AS InvoicedQuantity
                      , ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0) AS InvoicedAmount
                     FROM dbo.Invoice
                     LEFT JOIN (
                      SELECT dbo.InvoiceItem.InvoiceID
                       , dbo.InvoiceItem.ProductID
                       , ISNULL(SUM(dbo.InvoiceItem.Quantity), 0) AS InvoicedQuantity
                       , ISNULL(SUM(dbo.InvoiceItem.Quantity * dbo.InvoiceItem.Price), 0) AS InvoicedAmount
                      FROM dbo.InvoiceItem
                      GROUP BY dbo.InvoiceItem.InvoiceID
                       , InvoiceItem.ProductID
                      ) AS InvoiceItemDerived ON Invoice.InvoiceID = InvoiceItemDerived.InvoiceID
                     GROUP BY dbo.Invoice.SalesProposalID
                      , InvoiceItemDerived.ProductID
                   ) AS ProductBreakdown
       ) as derived
INNER JOIN dbo.Product ON derived.ProductID = dbo.Product.ProductID

Select allOpen in new window

This is the latest version of the code that I'm using. It's showing the proper details in terms of showig rows once. But, looking at it from the point of view that there are 2 derived tables, the data from each seem to be separated in the results. This may be because it's simply a union of the 2 derived tables. They need to merge then we'll have what we need.

WITH cte 
AS (SELECT * 
FROM dbo.SalesProposal SP 
INNER JOIN dbo.SalesProposalStatus SPS ON SP.StatusID = SPS.SalesProposalStatusID AND SPS.SalesProposalStatus = 'Sold'),
dispatchderived
AS (SELECT D.SalesProposalID, D.ProductID, ISNULL(SUM(D.Weight * D.ConversionFactor), 0) AS DeliveredQuantity 
FROM dbo.Dispatch D 
WHERE EXISTS (SELECT 1 FROM dbo.Product P WHERE D.ProductID = P.ProductID) 
GROUP BY D.SalesProposalID, D.ProductID), 
productbreakdown 
AS (SELECT I.SalesProposalID, InvoiceItemDerived.ProductID, ISNULL(SUM(InvoiceItemDerived.InvoicedQuantity), 0) AS InvoicedQuantity, ISNULL(SUM(InvoiceItemDerived.InvoicedAmount), 0)   AS InvoicedAmount 
FROM dbo.Invoice I 
LEFT OUTER JOIN (SELECT II.InvoiceID, II.ProductID, ISNULL(SUM(II.Quantity), 0) AS InvoicedQuantity, ISNULL(SUM(II.Quantity * II.Price), 0) AS InvoicedAmount
FROM dbo.InvoiceItem II GROUP BY II.InvoiceID, II.ProductID) AS InvoiceItemDerived 
ON I.InvoiceID = InvoiceItemDerived.InvoiceID 
WHERE EXISTS (SELECT 1 
FROM dbo.Product P 
WHERE P.productid = InvoiceItemDerived.ProductID) 
GROUP BY I.SalesProposalID, 
InvoiceItemDerived.ProductID)
SELECT c1.SalesProposalID, PB.ProductID, ISNULL(PB.InvoicedQuantity, 0) AS InvoicedQuantity, ISNULL(PB.InvoicedAmount, 0) AS InvoicedAmount, 0 DeliveredQuantity 
FROM cte c1 JOIN productbreakdown PB ON c1.SalesProposalID = PB.SalesProposalID 
UNION ALL 
SELECT c1.SalesProposalID, DD.ProductID, 0 InvoicedQuantity, 0 InvoicedAmount, ISNULL(DD.DeliveredQuantity, 0) AS DeliveredQuantity 
FROM cte c1 
JOIN dispatchderived DD ON c1.SalesProposalID = DD.SalesProposalID  

Select allOpen in new window

:)

"UNION ALL" will append and NOT merge (which is good for performance! not a criticism)

for an experiment put 2 dahes in front of "ALL" to make UNION --ALL in your code.

Is this result better?

Ideally one of the other suggestions would be adopted - but do try UNION

Same results with UNION --ALL


1BEA66B3-E3F2-4535-9191-1F20EF3225D5      A2910AA4-4D19-4497-AE25-C1F551DA578E      3000.00      171000.000000      0.0000
CFB5A1EC-CB1E-44E4-B594-55136681997D      A2910AA4-4D19-4497-AE25-C1F551DA578E      499.00      27195.500000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      0.00      0.000000      155.7270
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      0492E3A2-7490-4778-804B-0040971BADA9      62.57      2252.520000      0.0000
1CEAB3DB-1D31-4214-AF77-F6AB7AF110FA      31E94D82-0139-4542-84D0-BF035EB4A756      0.00      0.000000      2000.0000

This looks like it's right. I've tested with my own data and it looks OK. I'm deploying to a live testbed to check. If I want to add other joined details such as custoemr information I presume I have to add them into the group clause as well.

>>If I want to add other joined details such as customer information...
"that will depend"
you can "nest" the current group by and join other tables from it, but you want to be careful you don't re-expand the number of carefully reduced rows.

You could do this inside the CTE's? (then you have to carry through all additional fields into the final group by.)

There are 2 keys to play with (SalesProposalID, ProductID) to get more text with (e.g. client name, product name) - without intimate knowledge of the data model all I can do at this point is offer general advice.

I'm splitting the points. Sharath did the spade work and PortlePaul got it over the line. Good solution. Thanks.