SELECT DISTINCT...list?

[LNBruno]

Why oh why do I try to write code after 4pm on Fridays?

I've got a 3-column UserCustomerContact table:
userID customerID contactID
------ ---------- ---------
me 1 1
me 1 2
me 1 3

When I run this:

DECLARE @vcReturnValue varchar(100)

SELECT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(contactID AS varchar(10))
FROM UserCustomerContact
WHERE UserID = 'me'

...I get a comma separated list of contactIDs: 1, 2, 3
So far so good.

When I run this:

DECLARE @vcReturnValue varchar(100)

SELECT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(customerID AS varchar(10))
FROM UserCustomerContact
WHERE UserID = 'me'

...I get a comma separated list of customerIDs: 1, 1, 1
Not so good. What I really want is a list of DISTINCT customerIDs

This works for customerIDs (returns just the 1):

SELECT DISTINCT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(customerID AS varchar(10))
FROM UserCustomerContact
WHERE UserID = 'me'

...but this returns just the last record for contactID:

SELECT DISTINCT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(contactID AS varchar(10))
FROM UserCustomerContact
WHERE UserID = 'me'

Help?

< M!ke >

 

[gmmastros]

Try this...

SELECT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(contactID AS varchar(10))
FROM [!](Select Distinct contactId 
      From UserCustomerContact
      Where UserId = 'me') As A[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Ah... one more thing that you should add to all of these. It's the old NULL issue.

SELECT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(contactID AS varchar(10))
FROM (Select Distinct contactId 
      From UserCustomerContact
      Where UserId = 'me'
            [!]And contactID Is Not NULL[/!]) As A

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

DECLARE @Test TABLE (userID  varchar(2), customerID  int, contactID int)
INSERT INTO @Test VALUES('me',1,1)
INSERT INTO @Test VALUES('me',1,2)
INSERT INTO @Test VALUES('me',1,3)

DECLARE @vcReturnValue varchar(100)

SELECT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(contactID AS varchar(10))
FROM @Test UserCustomerContact
WHERE UserID = 'me'
GROUP BY contactID

SELECT @vcReturnValue

SET @vcReturnValue = NULL

SELECT @vcReturnValue = COALESCE(@vcReturnValue + ', ', '')
    + CAST(customerID AS varchar(10))
FROM @Test UserCustomerContact
WHERE UserID = 'me'
GROUP BY customerID

SELECT @vcReturnValue

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

BRILLIANT!

And on that note, let's call it a week and pop into the pub for a pint!

Thanks SO much!


< M!ke >