query with joined count....

[scarhand]

Having trouble...heres my query:

 

$sql = mysql_query("select p.title, p.date_posted, p.date_deadline, p.payment_deposit, 
                    p.payment_total, p.payment_status, p.priority, 
                    (select count(r.*) as replycount from projects as p, replies as r where r.pid = p.id)
                    from projects as p, replies as r") or die(mysql_error());

 

I'm just trying to grab the 7 fields from the projects table, and count how many replies there are for the project from the replies table.

 

r.pid = project id (p.id)

[scarhand]

this doesnt work properly either:

 

$sql = mysql_query("select p.*, count(r.pid) as replycount from projects as p, replies as r where p.id = r.pid");

[kickstart]

Hi

 

You need a GROUP BY clause. For example:-

 

SELECT p.title, p.date_posted, p.date_deadline, p.payment_deposit, p.payment_total, p.payment_status, p.priority, COUNT(r.pid) as replycount 
FROM projects p 
INNER JOIN replies r 
ON p.id = r.pid
GROUP BY p.title, p.date_posted, p.date_deadline, p.payment_deposit, p.payment_total, p.payment_status, p.priority

 

All the best

 

Keith

[scarhand]

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

This is the error I get using that query, kickstart

[kickstart]

Hi

 

Put the "or die" code back onto the mysql_query line so we can see what the real error is.

 

All the best

 

Keith

[scarhand]

got it working, you were right about the grouping

 

select p.*, count(r.pid) as replycount
from projects as p, replies as r
where r.pid = p.id
group by r.pid

[scarhand]

even better:

 

SELECT p.*, count(r.pid) replycount 
FROM projects p 
LEFT JOIN replies r ON r.pid = p.id
GROUP BY r.pid

[kickstart]

Hi

 

While MySQL doesn't mind having only only a few columns in the GROUP BY clause, plenty of other flavours will throw an error if you don't have all the non aggregate columns listed in the GROUP BY clause.

 

All the best

 

Keith