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
MySQL Count of related records
Moderator: General Moderators
Code: Select all
SELECT tbl1.id, count(tbl2.*) AS recCount
FROM tbl1
LEFT JOIN tbl2
ON tbl1.id = tbl2.id
GROUP BY tbl1.id
Hi
Thanks for your quick reply!
I can't get that to work, here's my complete query...
adbookings is the parent table, with adinserts being the secondary table.
The error I'm getting is...
Any ideas?
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_idThe 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 1Code: Select all
SELECT adbookings.*, COUNT(*) ............
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Often select count(*) is quicker if you just add one column (normally the primary key column)...
This can make a large difference for complex/large tables in speed.
Code: Select all
SELECT adbookings.*, COUNT(one_column) .................