By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,949 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,949 IT Pros & Developers. It's quick & easy.

join for three tables with grouping

P: n/a
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

Mar 2 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Rik
On Fri, 02 Mar 2007 18:19:48 +0100, john7 <jo******@fastermail.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
Mar 2 '07 #2

P: n/a
On Mar 2, 12:04 pm, Rik <luiheidsgoe...@hotmail.comwrote:
On Fri, 02 Mar 2007 18:19:48 +0100, john7 <johnm...@fastermail.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

Mar 2 '07 #3

P: n/a
Rik
john7 <jo******@fastermail.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
Mar 2 '07 #4

P: n/a
Rik
On Fri, 02 Mar 2007 19:48:29 +0100, Rik <lu************@hotmail.comwrote:
john7 <jo******@fastermail.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
Mar 2 '07 #5

P: n/a
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:
john7 <johnm...@fastermail.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

Mar 2 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.