Get record from table and record counts from 3 related table

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

Get record from table and record counts from 3 related table

Post by batfastad »

Hi everyone
I'm writing a simple intranet app to search through company contact information.

I have a main table called companies, then a few related tables: contacts, adbookings, invoices all related by a company_id

I have a simple SELECT query which gets the company information and displays it on the page:
[sql]SELECT `companies`.* FROM `companies` WHERE `companies`.`company_id`='19796' LIMIT 0, 1[/sql]

But I would also like to return the number of related records from the contacts, adbookings and invoices tables at the same time.

I know how to do the related record count from only a single related table:
[sql]SELECT `companies`.*, COUNT(`contacts`.`company_id`) AS `contacts_count` FROM `companies` LEFT JOIN `contacts` USING (`company_id`) WHERE `companies`.`company_id`='19796' GROUP BY `companies`.`company_id` LIMIT 0, 1[/sql]

But I'm having trouble extending this to get the related counts from multiple related tables.
Obviously I can only have a single GROUP BY

What's the proper way to do something like this?
I think there's some SQL syntax that I don't know about here, I'm only just starting out really.

Any suggestions?

Cheers, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get record from table and record counts from 3 related table

Post by VladSun »

You may use dependent subselects for every count() you need and I think it will be faster than counting distinct fields by using [sql]count(DISTINCT field1, field2, field3)[/sql] - http://dev.mysql.com/doc/refman/5.0/en/ ... t-distinct

I'm not sure about it so check it first :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Get record from table and record counts from 3 related table

Post by batfastad »

Ok great!
So where do they go?
Do I still need the GROUP BY and multiple LEFT JOINs?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get record from table and record counts from 3 related table

Post by VladSun »

Yes
Although I'm not sure what happens when DISTINCT and GROUP BY are mixed up together.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Get record from table and record counts from 3 related table

Post by batfastad »

Hello again VladSun :)

[sql]SELECT `companies`.*, COUNT(DISTINCT `adbookings`.`booking_id`) AS `count_adbookings`, COUNT(DISTINCT `contacts`.`contact_id`) AS `count_contacts`, COUNT(DISTINCT `invoices`.`invoice_id`) AS `count_invoices` FROM `companies` LEFT JOIN `adbookings` ON(`companies`.`company_id`=`adbookings`.`company_id`) LEFT JOIN `contacts` ON(`companies`.`company_id`=`contacts`.`company_id`) LEFT JOIN `invoices` ON(`companies`.`company_id`=`invoices`.`company_id`) WHERE `companies`.`company_id`='19796' GROUP BY `companies`.`company_id`LIMIT 0, 1[/sql]
I went for this and had some success, took 1.2s then changed some indexes into compound indexes and got it down to 0.2s

Still, that's not great performance just to get some record counts! PHP just sitting there for 0.2s waiting for MySQL data, I may as well just add another 20 lines of PHP and do each count as a separate query!

Then I looked into the UNION keyword, never used it before and didn't know what it did.

And doing this:
[sql]SELECT COUNT(*) FROM `adbookings` WHERE `adbookings`.`company_id`='19796' UNION SELECT COUNT(*) FROM `contacts` WHERE `contacts`.`company_id`='19796' UNION SELECT COUNT(*) FROM `invoices` WHERE `invoices`.`company_id`='19796' [/sql]
... is much quicker!

Is that an acceptable alternative?

Cheers, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get record from table and record counts from 3 related table

Post by VladSun »

Yeah, it was the solution I was going to offer you, but I saw that you wanted the `companies`.* info along with these counts.
You may write it a bit clearer:
[sql]SELECT 'adbookings', COUNT(*) FROM `adbookings` WHERE `adbookings`.`company_id`='19796' UNION SELECT 'contacts', COUNT(*) FROM `contacts` WHERE `contacts`.`company_id`='19796' UNION SELECT 'invoices', COUNT(*) FROM `invoices` WHERE `invoices`.`company_id`='19796'[/sql]

This way you will not rely on the SQL UNION order to get the desired "related-count"
Last edited by VladSun on Thu Jan 22, 2009 8:55 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Get record from table and record counts from 3 related table

Post by VladSun »

Another way to do it is:
[sql] SELECT `companies`.*, (SELECT COUNT(*) FROM `adbookings` WHERE `adbookings`.`company_id`=`companies`.company_id) AS adbooking_count, (SELECT COUNT(*) FROM `contacts` WHERE `contacts`.`company_id`==`companies`.company_id) AS contacts_count, (SELECT COUNT(*) FROM `invoices` WHERE `invoices`.`company_id`=`companies`.company_id) AS invoices_countFROM`companies`WHERE`companies`.company_id='423432' [/sql]

And maybe it will be faster if you put
[sql]WHEREcompany_id='423432'[/sql]
in all subselects instead of using a dependent subselect.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: Get record from table and record counts from 3 related table

Post by batfastad »

Sweet!
I like the SELECT 'adbookings', COUNT(*) version
Because then when I'm running through those records in PHP after the query, I can use crazy variable variables so they have dynamic names!

Thanks for your help VladSun as always ;)
Post Reply