"Rajeev" wrote:

> If i remove the added code and run the below query, i get the following
> output ( for fileid  1591673)
>
>
> SELECT
> HSA.Fileid,
> --PPA.CoordinatorLastNm,
> --PPA.CoordinatorFirstNm,
> PPA1.LastNm + ',' + PPA1.FirstNm AS CSCName,
> PPA1.ElectronicNo AS CSCEmailId
> --PPA2.LastNm + ',' + PPA2.FirstNm AS RCO
> FROM HOMESALE_ASSISTANCE HSA
> LEFT JOIN (SELECT
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
>             --TOP 1
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>             PA1.FileId,
>             P1.LastNm,
>             P1.FirstNm,
>             EA.ElectronicNo
>         FROM Person_Assignment PA1 (NOLOCK)
>     INNER JOIN Person P1(NOLOCK) ON PA1.PersonId = P1.PersonId
>     INNER JOIN IC2_Reporting..Electronic_Address EA (NOLOCK) ON
> PA1.PersonId = EA.PartyId
>     WHERE PA1.PartyRoleCd = 'PRRC' AND PA1.AssignInactiveDt IS NULL
>     AND EA.LocRoleCd='LEML'
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
> --    AND PA1.Assigndt IS NOT NULL
> --    ORDER BY PA1.Assigndt DESC
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>     ) PPA1
> ON HSA.FILEID = PPA1.FILEID
> WHERE HSA.fileid = 1591673
>
>
> Fileid      CSCName
>   CSCEmailId
>
>
>
> -----------
> -------------------------------------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 1591673     Banks,Aimee
>   Aimee.Ba***@cartus.com
>
> (1 row(s) affected)
>
>
>
> If i run only the SELECT Statement inside the Left join
>
> SELECT
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
>         --    TOP 1
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>             PA1.FileId,
>             P1.LastNm,
>             P1.FirstNm,
>             EA.ElectronicNo
>         FROM Person_Assignment PA1 (NOLOCK)
>     INNER JOIN Person P1(NOLOCK) ON PA1.PersonId = P1.PersonId
>     INNER JOIN IC2_Reporting..Electronic_Address EA (NOLOCK) ON
> PA1.PersonId = EA.PartyId
>     WHERE PA1.PartyRoleCd = 'PRRC' AND PA1.AssignInactiveDt IS NULL
>     AND EA.LocRoleCd='LEML' AND  PA1.fileid = 1591673
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
> --    AND PA1.Assigndt IS NOT NULL
>     --ORDER BY PA1.Assigndt DESC
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>
>
>
> FileId      LastNm                         FirstNm
>   ElectronicNo
>
>
>
> ----------- ------------------------------
> ------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 1591673     Banks                          Aimee
>   Aimee.Ba***@cartus.com
>
> (1 row(s) affected)
>
>
>
> If i uncomment my added code in the above query i m still getting the
> same result. below is the query
>
>
> SELECT
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
>             TOP 1
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>             PA1.FileId,
>             P1.LastNm,
>             P1.FirstNm,
>             EA.ElectronicNo
>         FROM Person_Assignment PA1 (NOLOCK)
>     INNER JOIN Person P1(NOLOCK) ON PA1.PersonId = P1.PersonId
>     INNER JOIN IC2_Reporting..Electronic_Address EA (NOLOCK) ON
> PA1.PersonId = EA.PartyId
>     WHERE PA1.PartyRoleCd = 'PRRC' AND PA1.AssignInactiveDt IS NULL
>     AND EA.LocRoleCd='LEML'
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
>     AND PA1.Assigndt IS NOT NULL AND PA1.fileid = 1591673
>     ORDER BY PA1.Assigndt DESC
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>
>
> FileId      LastNm                         FirstNm
>   ElectronicNo
>
>
>
> ----------- ------------------------------
> ------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 1591673     Banks                          Aimee
>   Aimee.Ba***@cartus.com
>
> (1 row(s) affected)
>
>
>
>
> But if i run the below complete query with my code i get the null value
>
>
>
> SELECT
> HSA.Fileid,
> --PPA.CoordinatorLastNm,
> --PPA.CoordinatorFirstNm,
> PPA1.LastNm + ',' + PPA1.FirstNm AS CSCName,
> PPA1.ElectronicNo AS CSCEmailId
> --PPA2.LastNm + ',' + PPA2.FirstNm AS RCO
> FROM HOMESALE_ASSISTANCE HSA
>
>
> LEFT JOIN (SELECT
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
>             TOP 1
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>             PA1.FileId,
>             P1.LastNm,
>             P1.FirstNm,
>             EA.ElectronicNo
>         FROM Person_Assignment PA1 (NOLOCK)
>     INNER JOIN Person P1(NOLOCK) ON PA1.PersonId = P1.PersonId
>     INNER JOIN IC2_Reporting..Electronic_Address EA (NOLOCK) ON
> PA1.PersonId = EA.PartyId
>     WHERE PA1.PartyRoleCd = 'PRRC' AND PA1.AssignInactiveDt IS NULL
>     AND EA.LocRoleCd='LEML'
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 Start*/
>     AND PA1.Assigndt IS NOT NULL
>     ORDER BY PA1.Assigndt DESC
> /*Added by vinay j --For duplicate files issue -- 2006-07-27 End*/
>     ) PPA1
> ON HSA.FILEID = PPA1.FILEID
> WHERE HSA.fileid = 1591673
>
>
> Fileid      CSCName
>   CSCEmailId
>
>
>
> -----------
> -------------------------------------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 1591673     NULL
>   NULL
>
> (1 row(s) affected)
>
>
>
> So something is wrong. Can any one tell me what is the problem and what
> should be the solution.
>
> I have to use left join only.. i can't use inner join (some business
> condition)
>
> Regards,
> Vinay
>
>