join for three tables with grouping

I have three tables one for categories, one for products1 and another
for products2. I am using PHP and MySQL.

so for example I
categories
categoryid name
1 programming
2 databases
3 os

products1
id name categoryid
1 php 1
2 mysql 2
3 linux 3
4 javascript 1

products2
id name categoryid
1 java 1
2 linux 3
3 windows 3

I need to join these tables such that I can get the count of name by
categories

for example the result table should like

categoryid (count name for products 1) (count name for products 2)
1 2 1
2 1 0
3 1 2

Is there a way to do get this result in one SQL statement?


Thanks
John

re: join for three tables with grouping

On Fri, 02 Mar 2007 18:19:48 +0100, john7 <johnmark@fastermail.comwrote:

Well, ask an SQL group or that one of your currently used database.... :P

Untested:

SELECT
c.`id` as 'categoryid',
IFNULL(COUNT(p1.`name`),0) as 'products1',
IFNULL(COUNT(p2.`name`),0) as 'products2',
FROM `categories` c
LEFT JOIN `products1` p1
ON c.`id` = p1.`categoryid`
LEFT JOIN `products2` p2
ON c.`id` = p2.`categoryid`
GROUP BY c.`id`

--
Rik Wasmus

re: join for three tables with grouping

On Mar 2, 12:04 pm, Rik <luiheidsgoe...@hotmail.comwrote:Hi Rik,

Thanks for your reply, the above statement is returning

categoryid (count name for products 1) (count name for products 2)
1 2 2
2 1 0
3 2 2

re: join for three tables with grouping

john7 <johnmark@fastermail.comwrote:Yup, just discovered that myself. Odd, as the manual states:
"COUNT() .. Returns a count of the number of non-NULL values in the rows"

And I'd think we'd only have null values with a left join...
Well, off to comp.databases.mysql it is, I see you multiposted to that
group already.
--
Rik Wasmus

re: join for three tables with grouping

On Fri, 02 Mar 2007 19:48:29 +0100, Rik <luiheidsgoeroe@hotmail.comwrote:
D'OH!
Now I know why this doesn't work.... GROUP BY is done in the end, so any
match will be repeated if the other join asks for more rows...
--
Rik Wasmus

re: join for three tables with grouping

I first posted to MySQL group but decided later that I will get faster
answers from the PHP group as this group is very active.
Thanks for taking time to look into this problem.

John

On Mar 2, 12:48 pm, Rik <luiheidsgoe...@hotmail.comwrote: