MySQL Query Join and Count Query?

You may want to try the following: SELECT i. Id, i. Industry, count(l.Id) as count FROM industries I LEFT JOIN ( SELECT l.

Industry, l. Id FROM links l JOIN companies c ON (l. Company = c.

Id AND c. Active = 1) ) l ON (l. Industry = i.

Id) WHERE i. Mod = 2 GROUP BY i.Id, i. Industry It should return the following result: id | industry | count | +------+-------------+-------+ | 2 | Roofing | 2 | | 4 | Carpentry | 2 | | 7 | Handyman | 1 | | 8 | Haulage | 0 | | 9 | Electrician | 0 | +------+-------------+-------+ 5 rows in set (0.00 sec).

You may want to try the following: SELECT i. Id, i. Industry, count(l.Id) as count FROM industries I LEFT JOIN ( SELECT l.

Industry, l. Id FROM links l JOIN companies c ON (l. Company = c.

Id AND c. Active = 1) ) l ON (l. Industry = i.

Id) WHERE i. Mod = 2 GROUP BY i.Id, i. Industry; It should return the following result: +------+-------------+-------+ | id | industry | count | +------+-------------+-------+ | 2 | Roofing | 2 | | 4 | Carpentry | 2 | | 7 | Handyman | 1 | | 8 | Haulage | 0 | | 9 | Electrician | 0 | +------+-------------+-------+ 5 rows in set (0.00 sec).

Incredible Daniel, thank you so much. – steveneaston Sep 7 '10 at 11:06.

The 2nd query (for active records only) is doing a cross join with Companies table. Try this - afraid I didn't test it but should work : SELECT industries. Id, industries.

Industry, count(links. Id) as count FROM industries LEFT JOIN links on links. Industry=industries.Id INNER JOIN companies on company.

Id = links. Company WHERE industries. Mod=2 AND companies.

Active=1 GROUP BY industries. Id EDIT : Added a query that should take care for case with Industry with O count SELECT industries.Id, industries. Industry, count(x.

Id) FROM industries JOIN ( SELECT links.Id, links. Industry, company. Id FROM companies INNER JOIN links on links.

Company = companies. Id WHERE companies. Active=1 ) x ON industries.Id = x.

Industry AND industries. Mod=2 GROUP BY industries.id.

Great thank you In Sane; That's working, but it's not returning a 0 count for industries without any companies (in this case Electrician, Haulage Ton-o-Bricks Haulage is inactive). I've tried adding IFNULL(COUNT(*),0) but that doesn't do the trick – steveneaston Sep 7 '10 at 10:31 How about replacing the INNER JOIN companies with LEFT JOIN companies. Can you try it out?

– InSane Sep 7 '10 at 10:33 I get the same results with LEFT JOIN (with and without the IFNULL(COU...), no fields returned with 0 values – steveneaston Sep 7 '10 at 10:42 hmm..i thought so..right after I posted it... my bad! Its the AND filter that is causing the problem...the LEFT join won't work.. – InSane Sep 7 '10 at 10:48 Thank you also for your help In Sane, much appreciated. – steveneaston Sep 7 '10 at 11:07.

Try this: SELECT industries. Id, industries. Industry, count(links.Id) as count FROM industries LEFT JOIN links on links.

Industry=industries. Id LEFT JOIN links. Company = companies.

Id WHERE industries. Mod=2 AND companies. Active=1 GROUP BY industries.id.

The 2nd query (for active records only) is doing a cross join with Companies table.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.