repeation in inner xml node - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

repeation in inner xml node

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-22 : 08:35:14

Hi,

In my subselect some of the values are repeating...i have already taken distinct,
but in this case it has different org name with same user_id but i have to get distinct
user_id details.. here i am copying only subquery details.. how i can resolve this

(
SELECT
DISTINCT
(
CASE WHEN Position_Education_Header.Position_ID<>0
THEN User_Address.Org_Name
WHEN Position_Education_Header.Education_ID<>0
THEN Address1.Org_Name
ELSE User.Org_Name
END
) AS Org_Name,
ISNULL(User.User_ID,'')AS User_ID,
COALESCE(Country1.Country_Name,_Country.Country_Name)AS City,
User.Last_Name
FROM User WITH(NOLOCK)
LEFT JOIN User_Education WITH(NOLOCK)
ON User_Education.User_ID=User.User_ID
LEFT JOIN User_Address Address1 WITH(NOLOCK)
ON UseAddress1.Address_ID=User_Education.Address_ID
LEFT JOIN User_Position WITH(NOLOCK)
ON User_Position.User_ID=User.User_ID

some more inner joins are also der,Please help with some ideas... :(

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 11:23:20

while making user_ID distinct you can return only one set of values for other fields, in such case which value you need to return for others? first,last or random? show with some example what you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-23 : 03:09:43

quote:
Originally posted by visakh16

while making user_ID distinct you can return only one set of values for other fields, in such case which value you need to return for others? first,last or random? show with some example what you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh,

I have resolved this issue, i took the repeating user_id and org name outside
to a CTE then in sunquery i have inner joined with the new cte and took userId and org anme in the
subquery

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:08:32

quote:
Originally posted by divyaram

quote:
Originally posted by visakh16

while making user_ID distinct you can return only one set of values for other fields, in such case which value you need to return for others? first,last or random? show with some example what you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh,

I have resolved this issue, i took the repeating user_id and org name outside
to a CTE then in sunquery i have inner joined with the new cte and took userId and org anme in the
subquery

Regards,
Divya

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources