Posted by Rik on 03/02/07 18:04

On Fri, 02 Mar 2007 18:19:48 +0100, john7 <johnmark@fastermail.com> wrot=
e:

> 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` =3D p1.`categoryid`
LEFT JOIN `products2` p2
ON c.`id` =3D p2.`categoryid`
GROUP BY c.`id`

-- =

Rik Wasmus

[Back to original message]