COUNT(*), MySQL, multiple tables - help!

[themoose]

blegh, nevermind, figured it out another way!

[JohnN]

Heres my normal way of approaching something like this:




$query = mysql_query("SELECT id, title FROM table_one WHERE (one.title LIKE '%Obama%' OR one.title LIKE '%Hilary%') ORDER BY one.timestamp DESC LIMIT 5");

$queryb = '1=2';

while($row = mysql_fetch_array($query)){
$queryb .= " OR title_id = '".$row['id.]"';
$results[$row['id']] = $row;
}

$query = mysql_query("SELECT SUM(positive) as positive, SUM(negative) as negative, title_id WHERE $queryb");
//for each title id get the sum of the positive and negative coloums

while($row = mysql_fetch_array($query)){

if(($row['positive'] - $row['negative']) > 0)
$new[] = $results[$row['title_id']];

}



$new should now be an array with your results with more positives than negatives.

low tech I know, but hell.

[themoose]

Ahh thanks, I know I said it was sorted but that could help reduce some queries :D

Cheers!

[Tree]

Out of curiosity, what was the original question?

[themoose]

Well, I don't think I asked it properly, but basically I wanted to make a condition in a statement where the numrows of another query minus the numrows of yet another query was greater than 0, and then consolidate it all into one.

[themoose]

Okay, I have another question.

Basically, in the query,
SELECT
(...lots of things...)
FROM
titles AS t,
sites AS s
WHERE ( long where statement )
AND (s.approved=1 WHERE s.id=t.siteid)

But of course, you can't have two "WHERE" clauses in one statement.. so how do I do this?

[Tree]

Replace the second WHERE with AND.