There you have it. If you put just the subID, Topic_ID and forumID in the
SELECT list, you will see that a SELECT DISTINCT will give you all distinct
values. If you want the subID's to be unique in the result set, you will
have to remove the columns from the other tables in the SELECT list.
--
Tom
----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
www.apress.com
---
There are multiple topics per subscription. You basically subscribe to a
particular forum and you will get all the topics to that forum.
> To diagnose this properly, we would need the DDL and sample data for all
> tables. While the subID may be unique in the subscriptions table, it is
> possible, that there are:
> multiple topics per subscription
> multiple members per topic
> multiple forums per topic
> The DISTINCT keyword applies to the entire row. Check your data and see
> where there are multiples. Check your output and see that the row itself
is
> unique.
> --
> Tom
> ----------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> www.apress.com
> ---
> I need help getting the DISTINCT keyword to work.
> I'm trying to return records with a unique subID value. I set the
DISTINCT
> keyword before the subID value but it still returns multiple subID's. Any
> help would be great. Thanks.
> Here is my stored procedure:
> Alter Procedure getSubscriptionDefs
> As
> select
> DISTINCT S.subID,
> S.forumID,
> S.topicID,
> S.memberID,
> S.Active,
> S.goInbox,
> S.goEmail,
> T.Topic_ID,
> M.M_Name,
> F.F_Name,
> SUBSTRING(T.T_Subject,0,50) as T_Subject,
> T.T_Originator,
> T.T_Date,
> T.T_Last_Post
> from subscriptions S
> join Topics T on (T.Topic_ID = S.topicID or T.T_Originator = S.MemberID or
> T.Forum_ID = S.forumID)
> left outer join Members M on Member_ID = T.T_Originator
> left outer join Forum F on F.Forum_ID = T.forum_ID
> ORDER BY T.T_Last_Post DESC,T.T_Date DESC
> return
by