Distinct wont work

[hc1619]

I have two tables (its a message board). One table is Topics, the other is TopicPostings. Topics contains the title of each discussion, date created etc and has ID called "TID". TopicPostings has all the postings.. it has a unique id "TPID", and also a "TID" column so i know which topic the posting belongs to.

What i want to do is display all the topics a member has posted in. A member though may have posted in a topic several times, so I only want to display that topic once, not 4, 5, 6 or however many times they posted to that topic.

I tried this below but it didn't work.

SELECT DISTINCT TopicPostings.TID, Topics.Subject
From TopicPostings
Inner join Topics
on TopicPostings.TID = Topics.TID
Where TopicPostings.MemberID = @memberID

I even tried removing the inner join and i just tried to get the distinct working on just TopicPostings, but still it brought up multiple results for topics that shared the same TID.. whats up!??

 

[vongrunt]

This query looks OK. There are some other ways to do the same thing (GROUP BY, derived table, IN/ANY/EXISTS) but problem is very likely not in query itself.

Are Subject values in result set always the same for each TID or not?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[hc1619]

the Topics table has only 1 row.. so the subject is the topic title and one TID to identify it. The TopicPOstings table has multiple rows.. so if a user has made 5 posting to one topic, it will have 5 rows.. all with the same TID in the TID column, linking it to the appropriate TID row in the Topics table. Yet it still brings back 5 results for each posting the user made, not one. It's like the DISTINCT is being completely ignored.

 

[vongrunt]

OK, can you post sample data?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[jgoodman00]

So if you try the following what do you get:

SELECT DISTINCT TID
From TopicPostings
Where TopicPostings.MemberID = @memberID

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[hc1619]

jgoodman - that code worked.. it brought up just the one result for each topic.. BUT, as soon as i added another column to the query, ie:

SELECT DISTINCT TID, DatePosted
From TopicPostings
Where TopicPostings.MemberID = @memberID

it fell over and brought up all the results.. ignored the DISTINCT...???

 

[r937]

hc1619, DISTINCT applies to all columns selected, i.e. the entire row

so obviously if a single TID has more than one date associated with it, your query will, as you say, "fall over"

however, DISTINCT is working correctly

what you could do is show the latest date per topic

SELECT TID, max(DatePosted) as lastpost
From TopicPostings
Where TopicPostings.MemberID = @memberID
group by TID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)