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

Transact-SQL
1

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"

2

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.

3

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
4

Thank you, this worked perfectly...