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
Get record from table and record counts from 3 related table
Moderator: General Moderators
Re: Get record from table and record counts from 3 related table
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
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
Re: Get record from table and record counts from 3 related table
Ok great!
So where do they go?
Do I still need the GROUP BY and multiple LEFT JOINs?
So where do they go?
Do I still need the GROUP BY and multiple LEFT JOINs?
Re: Get record from table and record counts from 3 related table
Yes
Although I'm not sure what happens when DISTINCT and GROUP BY are mixed up together.
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
Re: Get record from table and record counts from 3 related table
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
[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
Re: Get record from table and record counts from 3 related table
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"
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
Re: Get record from table and record counts from 3 related table
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.
[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
Re: Get record from table and record counts from 3 related table
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
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