efficient query help please.

[SDB]

a) If i have a threadid, what is the most efficient way to find out if a specific userid has posted in that thread?

b) if i have a query which returns a list of threadids, what is the most efficient join or similar to use in order to establish on a line by line basis whether the same specific userid has posted in that thread?

Thanks in advance.

Simon

[Eikinskjaldi]

Originally Posted by SDB

a) If i have a threadid, what is the most efficient way to find out if a specific userid has posted in that thread?

b) if i have a query which returns a list of threadids, what is the most efficient join or similar to use in order to establish on a line by line basis whether the same specific userid has posted in that thread?

Thanks in advance.

Simon

a) $foo = $db->query_first("select postid from post where userid=blah1 and threadid=blah2")
if ($foo) {user/thread found}

b) $q = $db->query_read("select distinct threadid from post where userid=blah1 and threadid in (list of theads)")

while ($res=$db->fetch_array($q)) {
$res[threadid]....

[SDB]

hmmm.. ok, option a) thank you, that's what I have now.

option b) isn't quite what I meant..

What i meant is...

if I already have a query such as :

SELECT threadid from specialtable ....

what's the most efficient way include a column in the returned results which establishes whether userid x has posted in that thread.

So I would end up with results with columns [specialtable.threadid] and [useridhasposted or useridpostcount or similar]

Thanks in advance

Simon

[Eikinskjaldi]

Originally Posted by SDB

hmmm.. ok, option a) thank you, that's what I have now.

option b) isn't quite what I meant..

What i meant is...

if I already have a query such as :

SELECT threadid from specialtable ....

SELECT sp.threadid, postid, count(*) as 'postcount'
FROM specialtable sp
LEFT OUTER JOIN post USING (threadid)
WHERE userid=blah
GROUP BY sp.threadid

the [postid] field will contain null if there are no posts, but the count will still be 1 (because there is 1 row in the result table

if you want to ignore null results then just do a join instead of a left outer join