SQLTeam.com | Weblogs | Forums

Getting Multiple rows

Getting multiple rows here, One of my joins is causing issue, can't figure out

 Select *
   TRIM(AD.PADLN2)||','||TRIM(AD.PADLN1)||','||TRIM (AD.PADCTY)||','||TRIM(AD.PADRGN)||','||TRIM(AD.COUCOD)||','||TRIM (AD.PSLCOD) AS PERADD,
   TRIM(PRN.FRSNAM)||','||TRIM(PRN.LSTNAM)||','||TRIM (PRN.MDLINL)||','||TRIM(PRN.NAMMFR) AS PERNAM,

   CASE C.CARSTS 
    WHEN 0 THEN 'ACTIVE'
          ELSE 'INACTIVE' 
   END AS CARSTS,
   
   CASE M.MSPTYP 
    WHEN 1 THEN 'PRIMARY'
    WHEN 2 THEN 'DOMESTIC' 
   END AS MSPTYP, 
  
   CASE C.CARTYP 
    WHEN 1 THEN 'GOLDSTAR' 
   END AS CARTYP,        
  
   CASE M.MSPSTS 
    WHEN 0 THEN 'ACTIVE'
      ELSE 'INACTIVE' 
   END AS MSPSTS,
  
   CASE A.CPYNBR 
    WHEN 1 THEN 'US' 
    WHEN 4 THEN 'CA' 
   END AS  CPYNBR
   
   FROM P23ADTDT1.MEMSRLP M                                                          /* MEMBER RELATIONSHIP FILE */
   
    INNER JOIN P23ADTDT1.MEACBSP A 
       ON M.ACCIPK = A.ACCIPK                             /* ACCOUNT FILE */
    INNER JOIN P23ADTDT1.MECABSP C 
       ON M.ACCIPK = C.ACCIPK 
      AND C.LKFLCD = 100          /* CARD BASIS FILE */
     LEFT OUTER JOIN P23ADTDT1.MEPRIDP PR 
       ON M.LNKIPK = PR.PERIPK                      /* PERSONAL IDENTIFICATION FILE */
     LEFT OUTER JOIN P23ADTDT1.MEPHNBP P 
       ON M.LNKIPK = P.LNKIPK 
      AND P.LKFLCD = 100     /* PHONE NUMBERS FILE */
     LEFT OUTER JOIN P23ADTDT1.MEEMADP E 
       ON M.LNKIPK = E.LNKIPK 
      AND E.LKFLCD = 100     /* E-MAIL FILE */
     LEFT OUTER JOIN P23ADTDT1.MEPRNMP PRN 
       ON M.LNKIPK = PRN.PERIPK                    /* PERSONAL NAMES FILE */
     LEFT OUTER JOIN P23ADTDT1.MEPLADP AD 
       ON M.LNKIPK = AD.LNKIPK 
      AND AD.LKFLCD = 100  /* POSTAL ADDRESS FILE */
  
   WHERE M.MSPIPK = CAST(IO_MSPIPK AS DECIMAL (11,0 ) ) 
     AND M.LKFLCD  = 100 WITH  UR;

This is a SQL Server / T-SQL forum. Your SQL is not T-SQL. You might have better responses on a site specifically for the SQL engine you're using.

Start by commenting out the calculated columns - leaving just the *...

Remove all tables except the first 2...verify you are not getting duplicates. Add in the next table - check again...repeat until you find which table/join is causing duplicate values to be returned.

If I had to guess - and I do because I don't know your system - I would suspect either the phone, email or postal address since a person can have many of each.