How to avoid Duplication in query output

[Former Member]

This is a simple query I've wrote for finding Customer Information.There are duplication in this query if the  more than one contact persons entered to the fields(OCPR) .How can i avoid this from the output??Please see the sample output attached for any reference. SELECT  T0.[CardCode], T0.[CardName],OCRG.GroupName,T0.Password AS 'Product& Year',OSHP.TrnspName as 'Constitution',CRD7.TaxId0,CRD7.TaxId1,CRD7.TaxId11,CRD7.ECCNo,CRD7.CERegNo,CRD7.CERange,CRD7.CEDivis,CRD7.CEComRate,OOND.IndName,OSLP.SlpName, T1.[Name],T1.[FirstName], T1.[MiddleName], T1.[LastName],  T1.[Position], T1.[Address], T1.[Tel1], T1.[Tel2], T1.[Cellolar], T1.[Fax], T1.[E_MailL], T1.[Pager], T1.[Notes1] as 'Bday', T1.[Notes2] as 'Wedding' FROM OCRD T0  INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode INNER JOIN OSLP ON  T0.SLPCODE = OSLP.SLPCODE INNER JOIN OCRG ON T0.GroupCode = OCRG.GroupCode INNER JOIN OOND ON T0.IndustryC = OOND.IndCode INNER JOIN CRD7 ON T0.CardCode = CRD7.CardCode INNER JOIN OSHP ON T0.ShipType = OSHP.TrnspCode WHERE T0.CardType = '[%0]'

sasAS.txt.zip

[former_member280812 · Accepted solution]

Hi Nabil,

I think the only way not to duplicate the row in your output is to delete the other contact saved in your OCPR table..

Regards.

[Former Member · Accepted solution]

careLess aucim, It is not possible. We need all the contacts ,any functions available in sql like 'distinct'  to avoid duplicates??

[former_member280812 · Accepted solution]

Hi Nabil,

Yah distinct functions can remove duplicates but theres no way to use distinct function in your case. Because you want to display all the contacts and contact is link in the bp table. If you want use crystal report to get around with it use the supress function or you group it.

[KennedyT21]

Hi Nabil.

Write sub query for the contact person instead of linking to main query...

You can also use distinct but no use i think...

Cheers!

[Former Member]

Kennedy , How to write subquery? Can you please provide me the code??

[KennedyT21]

Try this way

SELECT Distinct T0.[CardCode],

       T0.[CardName],

       OCRG.GroupName,

       T0.Password AS 'Product& Year',

       OSHP.TrnspName AS 'Constitution',

       CRD7.TaxId0,

       CRD7.TaxId1,

       CRD7.TaxId11,

       CRD7.ECCNo,

       CRD7.CERegNo,

       CRD7.CERange,

       CRD7.CEDivis,

       CRD7.CEComRate,

       OOND.IndName,

       OSLP.SlpName ,

( SELECT TOP 1  Name FROM ocpr WHERE CardCode=t0.cardcode) AS Name

FROM   OCRD T0

       left JOIN OSLP

            ON  T0.SLPCODE = OSLP.SLPCODE

       INNER JOIN OCRG

            ON  T0.GroupCode = OCRG.GroupCode

       left JOIN OOND

            ON  T0.IndustryC = OOND.IndCode

    LEFT outer JOIN CRD7

            ON  T0.CardCode = CRD7.CardCode

       INNER JOIN OSHP

            ON  T0.ShipType = OSHP.TrnspCode

Regards

[former_member280812]

Hi kennedy,

Your query can remove the duplicate rows but i think it does not meet the needed of nabil because he wants to display all the contacts. As i understand with your query it will display just the first contact. I am right? Hehe

Regards.