First result doubled

Hi Guys,

I'm working on a the COALESCE portion of this query as I need my Entity.Name grouped in the same column for an export.  However, it is returning the first value twice.  It appears the LEFT OUTER JOIN and INNER JOIN statements are the culprit, but I need them to join the data properly.

So what I need:  ENTITY1,ENTITY56,ENTITY58

What I am getting:  ENTITY1,ENTITY1,ENTITY56,ENTITY58

The following code with its OUTER / INNER JOIN is the same code used in another proceeding select statement, which I need to use @AccessGroup to insert it into.

Any insight into why this is happening sequentially and a better way to go about this would be appreciated.

Thank you

***********

DECLARE    @AccessGroup VARCHAR(8000);

SELECT @AccessGroup = COALESCE(@AccessGroup + ', ', '') + Entity.Name
FROM
Cardholder

  LEFT OUTER JOIN Credential ON (Credential.Cardholder = Cardholder.Guid)
  INNER JOIN CardholderMembership ON (Cardholder.Guid = CardholderMembership.GuidMember)
  INNER JOIN CardholderGroup ON (CardholderMembership.GuidGroup = CardholderGroup.Guid)
  INNER JOIN Entity ON (CardholderGroup.Guid = Entity.Guid)

***********

September 1st, 2015 5:26pm

There is no need to LEFT JOIN with the Credential at all in your query.

Also, to generate comma delimited list it's better to use FOR XML PATH('') approach rather than relying on the above statement which is undocum

Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 5:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics