SQLTeam.com | Weblogs | Forums

How to designate a specific Match in a queries without getting multiple duplicates

My original query works:
/****** Pull Units Sold to Customers from POSDOCUMENT and POSDOCUMENTDETAIL Tables each day ******/
SELECT POSDocument.DocumentID, POSDocumentDetail.POSDocument_ID AS Expr1, POSDocumentDetail.LineType, POSDocumentDetail.ItemNumber, POSDocumentDetail.Description, POSDocumentDetail.ItemID, POSDocument.CustomerName,
POSDocumentDetail.isVoid, POSDocument.Customer_ID
FROM POSDocument INNER JOIN
POSDocumentDetail ON POSDocument.POSDocument_ID = POSDocumentDetail.POSDocument_ID
WHERE (POSDocumentDetail.LineType = 5) AND (POSDocument.DocumentType = 4) AND (POSDocumentDetail.isVoid = 0) AND (DATEDIFF(day, POSDocumentDetail.AddedByDate, GETDATE()) = 3)

It provides me 20 lines of data****

The query I would like to add to the above query:
SELECT Contact_Mechanism_ID, Party_ID, ContactMechanismType, ContactMechanismPurpose, Electronic_Address, IsPrimary
FROM ContactMechanism

***********There could be multiple "Electronic_Address" entries for the same "Party_ID". What I need is only the "Electronic_Address" for the "Party_ID" where "IsPrimay" = "1" ****************

"Party_ID correlates with "POSDocument.Customer_ID"

When I perform an additional join from the ContactMechanism table, I receive 148 lines of data because of all the duplicates that are being produced.***

SELECT POSDocument.DocumentID, POSDocument.POSDocument_ID, POSDocument.DocumentType, POSDocument.InvoiceNumber, POSDocumentDetail.POSDocumentDetail_ID, POSDocumentDetail.POSDocument_ID AS Expr1,
POSDocumentDetail.LineType, POSDocumentDetail.ItemNumber, POSDocumentDetail.Description, POSDocumentDetail.ItemID, POSDocument.CustomerName, POSDocument.Customer_ID, POSDocumentDetail.isVoid,
ContactMechanism.Party_ID, ContactMechanism.Electronic_Address, ContactMechanism.IsPrimary
FROM POSDocument INNER JOIN
POSDocumentDetail ON POSDocument.POSDocument_ID = POSDocumentDetail.POSDocument_ID INNER JOIN
ContactMechanism ON POSDocument.Customer_ID = ContactMechanism.Party_ID
WHERE (POSDocumentDetail.LineType = 5) AND (POSDocument.DocumentType = 4) AND (POSDocumentDetail.isVoid = 0) AND DATEDIFF(day,POSDocumentDetail.AddedByDate ,GETDATE()) = 3

How can I get
INNER JOIN
ContactMechanism ON POSDocument.Customer_ID = ContactMechanism.Party_ID to only show me the records that have "IsPrimary" = "1"

It is really difficult to read your query - it needs to be formatted and place in a code block. To put the code into a code block - use three back-ticks before the code and three back-ticks after the code.

To your question - since this is an INNER JOIN then just add the criteria to the WHERE clause.

If you know for sure that the row in ContactMechanism will always be there, you can change the "OUTER APPLY" to a "CROSS APPLY":

SELECT 
    PD.DocumentID, PDD.POSDocument_ID AS Expr1, PDD.LineType, 
    PDD.ItemNumber, PDD.Description, PDDetail.ItemID, 
    PD.CustomerName, PDD.isVoid, PD.Customer_ID,
    CM.Electronic_Address
FROM POSDocument PD INNER JOIN
POSDocumentDetail PDD ON PD.POSDocument_ID = PDD.POSDocument_ID OUTER APPLY (
    SELECT TOP (1) *
    FROM ContactMechanism CM
    WHERE CM.Part_ID = PD.Customer_ID AND
        IsPrimary = 1
) AS CM
WHERE (PDD.LineType = 5) AND (PDocument.DocumentType = 4) AND (PDD.isVoid = 0) AND 
    (DATEDIFF(day, POSDocumentDetail.AddedByDate, GETDATE()) = 3)
1 Like

Thank you, this worked perfectly...