MySQL Count of related records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL Count of related records

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

SELECT adbookings.*, COUNT(*) ............
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post by batfastad »

Excellent, works a treat!!

I definitely need to read up on doing more advanced joins.
Thanks for your help guys
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post by batfastad »

Thanks, this is all excellent info! :lol:
Post Reply