Page 1 of 1

MySQL Count of related records

Posted: Fri Apr 20, 2007 9:58 am
by batfastad
Hi everyone

When doing a MySQL query, is it possible to also get the number of related records?

I have 2 tables related by an id field and when I loop through the records in the master table it would be really useful to also list the number of related records that are present in the secondary table.

One way I thought was to just loop through the secondary table and build an array manually of the info from the master table, counting the records as I go.

But I was wondering if there's a magical way to do it in MySQL?


Thanks
Ben

Posted: Fri Apr 20, 2007 12:26 pm
by Begby

Code: Select all

SELECT tbl1.id, count(tbl2.*) AS recCount
FROM tbl1
LEFT JOIN tbl2
ON tbl1.id = tbl2.id
GROUP BY tbl1.id

Posted: Fri Apr 20, 2007 12:37 pm
by batfastad
Hi
Thanks for your quick reply!

I can't get that to work, here's my complete query...

Code: Select all

SELECT *, COUNT(adinserts.*) AS reccount
FROM adbookings
LEFT JOIN adinserts
ON adbookings.booking_id = adinserts.booking_id
GROUP BY adbookings.booking_id
adbookings is the parent table, with adinserts being the secondary table.

The error I'm getting is...

Code: Select all

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) AS reccount  FROM adbookings  LEFT JOIN adinserts  ON adbookings . booking_i' at line 1
Any ideas?

Posted: Fri Apr 20, 2007 1:52 pm
by Weirdan

Code: Select all

SELECT adbookings.*, COUNT(*) ............

Posted: Mon Apr 23, 2007 5:08 am
by batfastad
Excellent, works a treat!!

I definitely need to read up on doing more advanced joins.
Thanks for your help guys

Posted: Mon Apr 23, 2007 7:05 am
by CoderGoblin
Often select count(*) is quicker if you just add one column (normally the primary key column)...

Code: Select all

SELECT adbookings.*, COUNT(one_column) .................
This can make a large difference for complex/large tables in speed.

Posted: Mon Apr 23, 2007 7:42 am
by batfastad
Thanks, this is all excellent info! :lol: