SELECT lowest unique value

[AdeleMB]

Hi everyone,

hopefully this is a really easy question (LOL are they ever?!)

I have a select statement as follows:

var sql = 'SELECT DISTINCT watchThreads.masterID, watchThreads.userID, threads.masterID, threads.forumID, threads.userID, threads.subject, threads.dateUpdated, threads.parentID, Threads.threadID FROM watchThreads, threads WHERE watchThreads.userID = '+ client.userID +' AND Threads.masterID = WatchThreads.masterID ORDER BY threads.dateUpdated DESC; '

This works fine, however, I also need it to only return the thread with the lowest unique threadID (from the Threads.threadID table and column).

At the moment, this select statement will pull back multiple threads that meet the query e.g. it will return one thread AND all responses to that thread. I just need the thread which has the lowest threadID value i.e. the original posting, not the responses.

Is there a way that you can specify in the select statement, that you only the threads with the lowest value?

I have googled it with no joy and not sure how to proceed, can anyone help please?

Hope that all makes sense and let me know if more info is needed (but i cant reply til back at work on monday).

Thanks in advance!!

 

[PHV]

Could you please post some input samples and expected result ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AdeleMB]

Hi,
Im fairly new to this so i hope this is what you mean:

The WatchThreads table has two columns, masterID and userID. Each contains a system generated number.
e.g this is an example.
MasterID: 65, UserID: 323

The Threads table has numberous columns including threadID, forumID, masterID, parentID, subject, message, dateUpdated etc.
e.g this is an example.
threadID: 203, forumID: 163, masterID: 65, parentID: 202, subject: Help please!, Message: Help needed please, dateUpdated: 19/09/2007 19:40:01

The select statement picks the watchThreads where the userID matches the userID of the person logged in. It then takes the masterID of the watchThreads and looks for the same number in the threads tables columns. It then pulls back all these results that match.

The problem is that I get back numerous threads that all belong to the same topic e.g. i get the first message, then the response, then all responses that follow as they all have the same masterID number.
I just want the message that has the lowest parentID number (or 0) as this will be the earliest posted message in that topic.
(Or perhaps I need to specify only the message with the earliest date is returned? I dont know.)

I just need to narrow down the select statement somehow but dont know where to start

I hope that is what you were asking for? Sorry for being a noob

Many thanks!

 

[PHV]

Something like this ?
var sql = 'SELECT W.masterID, W.userID, T.masterID, T.forumID, T.userID, T.subject, T.dateUpdated, T.parentID, T.threadID FROM watchThreads W INNER JOIN threads T ON W.masterID = T.masterID INNER JOIN (SELECT MasterID, MIN(threadID) minID FROM threads) M ON T.masterID = M.masterID AND T.threadID = M.minID WHERE W.userID = ' + client.userID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AdeleMB]

Hi,

thanks so much for that, it gives me a good idea of where to go next. Ill have a fiddle around and hopefully get it working very soon!
Thanks,
Adele