WHERE COUNT(table2.id) > 0?

[newbtophp]

SELECT table1.title, table1.status FROM table1, table2 WHERE table1.author = '{$username}' AND COUNT(table2.id) > 0 AND table2.deleted = 0

 

But I get an error, but when I remove the ' AND COUNT(table2.id) > 0' the error goes; theirfore whats the correct way to do this?

 

[Pikachu2000]

What is the error?

[kickstart]

Hi

 

First problem is that you need to join the tables on a field. What you have is a cross join, and will bring back every combination of the rows from the 2 tables.

 

As to checking an aggregate value, use a GROUP BY to group the records you want to count and use a HAVING clause to check the value.

 

All the best

 

Keith

[newbtophp]

Hi

 

First problem is that you need to join the tables on a field. What you have is a cross join, and will bring back every combination of the rows from the 2 tables.

 

As to checking an aggregate value, use a GROUP BY to group the records you want to count and use a HAVING clause to check the value.

 

All the best

 

Keith

 

 

 

    $pm_query = "SELECT table2.id FROM table1, table2 WHERE table2.id = table1.id AND table1.author = '{$username}'";        $pm_reply_count = mysql_num_rows(mysql_query($pm_query));    //final query (which shall be used within a while loop)    $pm_id_query = "SELECT id FROM table1 WHERE (id = '{$id}' AND author = '{$username}' OR pm_author = '{$username}' AND {$pm_reply_count} > 0) AND deleted = 0";

 

 

I've come up with the above? but its kinda hackish? - their must be a better way I'm just unsure on how to

[ignace]

You should explain what you are trying to do, helping without context is difficult as I am sure the below won't give you the desired output also note that using aggregate functions implies GROUP BY

 

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

 

SELECT table1.title, table1.status
FROM table1, table2
WHERE table1.author = '{$username}' AND table2.deleted = 0
GROUP BY table2.id
HAVING count(table2.id) > 0

[kickstart]

Hi

 

You can merge them like this:-

 

SELECT table1.id, COUNT(table2,id)

FROM table1 INNER JOIN table2 ON table1.id = table2.id

WHERE table1.id = '{$id}'

AND (table1.author = '{$username}'

OR table1.pm_author = '{$username}' )

AND table1.deleted = 0

GROUP BY table1.id

 

Not sure on checking author / pm_author and deleted, so you might need to move the brackets around depending on exactly what you want.

 

All the best

 

Keith