Posted by john7 on 03/02/07 18:37

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

• Next in forum: Re: Length of MP3?
• Prev in forum: Re: Pulling Title Tag out of HTML
• Thread view: Re: join for three tables with grouping

[Reply to this message]