join for three tables with grouping

[john7]

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

[Rik]

Re: join for three tables with grouping

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

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?

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

[john7]

Re: join for three tables with grouping

On Mar 2, 12:04 pm, Rik <luiheidsgoe... @hotmail.comwro te:

On Fri, 02 Mar 2007 18:19:48 +0100, john7 <johnm...@faste rmail.comwrote:

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?

>
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- Hide quoted text -
>
- Show quoted text -

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

[Rik]

Re: join for three tables with grouping

john7 <johnmark@faste rmail.comwrote:

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

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

[Rik]

Re: join for three tables with grouping

On Fri, 02 Mar 2007 19:48:29 +0100, Rik <luiheidsgoeroe @hotmail.comwro te:

john7 <johnmark@faste rmail.comwrote:

> 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

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

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

[john7]

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.comwro te:

john7 <johnm...@faste rmail.comwrote:

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

>
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