Here is a piece of the code. I hope this helps. I'm getting desperate to
find the answer, and your explanation makes sense so far.

(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Alternative Health' THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID = mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Alternative Health') AS T1 ON T1.MemberID =
Age.memberID
LEFT OUTER JOIN
(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Men''s Health' THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID = mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Men''s Health') AS T2 ON T2.MemberID = Age.memberID
LEFT OUTER JOIN
(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Mental Health' THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID = mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Mental Health') AS T3 ON T3.MemberID = Age.memberID







-----Original Message-----
From: Hyland Ron [mailto:[Email address protected]
Sent: Friday, August 31, 2007 11:58 AM
To: [Email address protected]
Subject: Re: Select Distinct Values

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text


Are you joining to another table and if so what are your join criteria.
Often the cause of this is multiple hits on records in a child table. Even
if you are not selecting fields from that table it is enough to cause the
records to repeat.



-----Original Message-----
From: Carel Greaves [Email address protected]
To: LazyDBA Discussion [Email address protected]
Sent: Fri Aug 31 04:38:35 2007
Subject: Select Distinct Values

Just a question



I have a query that selects profile data for members, if I don't do a select
distinct it gives me a lot of correct values, (unique values) of members
i.e. only one record per member, but every now and then I get duplicate
values for one member, multiple times.



Why does this occure?



I know SELECT DISTINCT is there to remove duplicates, but without SELECT
DISTINCT why would this "mistake" happen?



Any help would be greatly appreciated.



Kind Regards

Carel Greaves





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page