[SOLVED] JOIN WHERE

[rarebit]

Hi,

Heres my current query:

$s = "SELECT title, ".$tnT.".id, blurb, COUNT(topic_id) as num    
FROM ".$tnT." 
LEFT JOIN ".$tnW." ON ".$tnW.".topic_id = ".$tnT.".id
GROUP BY ".$tnT.".id
ORDER BY weight";

However I only want to count the 'topic_id' where it's 'last' property is above a certain time, e.g. 'WHERE last > 0', i've tried a few ways but all to no avail...

$s = "SELECT title, ".$tnT.".id, blurb, COUNT(topic_id WHERE last > 0) as num
FROM ".$tnT." 
LEFT JOIN ".$tnW." ON ".$tnW.".topic_id = ".$tnT.".id
GROUP BY ".$tnT.".id
ORDER BY weight";

and...

$s = "SELECT title, ".$tnT.".id, blurb, COUNT(topic_id) as num    
FROM ".$tnT." 
{ LEFT JOIN ".$tnW." ON ".$tnW.".topic_id = ".$tnT.".id WHERE ".$tnW.".last >= 0 }
GROUP BY ".$tnT.".id
ORDER BY weight";

and...

$s = "SELECT title, ".$tnT.".id, blurb, COUNT(topic_id) as num    
FROM ".$tnT." 
LEFT JOIN ".$tnW." ON ".$tnW.".topic_id = ".$tnT.".id
WHERE ".$tnW.".last >= ".($time-30)."
GROUP BY ".$tnT.".id
ORDER BY weight";

(this last one sort of does it, but doesn't return one's without a valid 'last', therefore I changed:

WHERE ".$tnW.".last >= ".($time-30)." || ".$tnT.".id > 0

but that invalidates the 'last' bit...

 

Any suggestions on how to do a selective join please?

[Barand]

try

$s = "SELECT title, $tnT.id, blurb, COUNT(topic_id) as num   
        FROM $tnT
        LEFT JOIN $tnW ON $tnW.topic_id = $tnT.id
            AND $tnW.last >= ($time-30)
        GROUP BY $tnT.id
        ORDER BY weight";

[rarebit]

oooh, I like it...

 

A great big cheary thanks for Barand!