join for three tables with grouping

I have three tables one for categories, one for products1 and another
for products2

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

On Mar 2, 5:16 pm, "john7" <johnm...@fastermail.com> wrote:
> I have three tables one for categories, one for products1 and another
> for products2
>
> 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

What version are you using?
This looks like a structural error. Why have you got two tables
holding the same kind of data? Further, what happens if a product
belongs to more than 1 category?

That said, this is easy if you use subqueries, but just trying to do
it with joins (I think) is going to be more tricky.

On Mar 2, 11:58 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 2, 5:16 pm, "john7" <johnm...@fastermail.com> wrote:
>
>
>
>
>
> > I have three tables one for categories, one for products1 and another
> > for products2
>
> > 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
>
> What version are you using?
> This looks like a structural error. Why have you got two tables
> holding the same kind of data? Further, what happens if a product
> belongs to more than 1 category?
>
> That said, this is easy if you use subqueries, but just trying to do
> it with joins (I think) is going to be more tricky.- Hide quoted text -
>
> - Show quoted text -

I am using MySQL 5.
The tables above are just an example. I was only providing familiar
looking tables here.

How can it be done by subqueries?

John

On Mar 2, 6:10 pm, "john7" <johnm...@fastermail.com> wrote:
> On Mar 2, 11:58 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On Mar 2, 5:16 pm, "john7" <johnm...@fastermail.com> wrote:
>
> > > I have three tables one for categories, one for products1 and another
> > > for products2
>
> > > 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
>
> > What version are you using?
> > This looks like a structural error. Why have you got two tables
> > holding the same kind of data? Further, what happens if a product
> > belongs to more than 1 category?
>
> > That said, this is easy if you use subqueries, but just trying to do
> > it with joins (I think) is going to be more tricky.- Hide quoted text -
>
> > - Show quoted text -
>
> I am using MySQL 5.
> The tables above are just an example. I was only providing familiar
> looking tables here.
>
> How can it be done by subqueries?
>
> John

Although there may be a simpler way, all I can think of is this:

SELECT a.categoryid, count1, count2
FROM categories a
LEFT JOIN (

SELECT c.categoryid, count( p1.name ) count1
FROM categories c
LEFT JOIN products1 p1 ON p1.categoryid = c.categoryid
GROUP BY p1.categoryid
)x ON a.categoryid = x.categoryid
LEFT JOIN (

SELECT c.categoryid, count( p2.name ) count2
FROM categories c
LEFT JOIN products2 p2 ON p2.categoryid = c.categoryid
GROUP BY p2.categoryid
)y ON y.categoryid = x.categoryid
LIMIT 0 , 30

On Mar 2, 6:22 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 2, 6:10 pm, "john7" <johnm...@fastermail.com> wrote:
>
>
>
> > On Mar 2, 11:58 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > On Mar 2, 5:16 pm, "john7" <johnm...@fastermail.com> wrote:
>
> > > > I have three tables one for categories, one for products1 and another
> > > > for products2
>
> > > > 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
>
> > > What version are you using?
> > > This looks like a structural error. Why have you got two tables
> > > holding the same kind of data? Further, what happens if a product
> > > belongs to more than 1 category?
>
> > > That said, this is easy if you use subqueries, but just trying to do
> > > it with joins (I think) is going to be more tricky.- Hide quoted text -
>
> > > - Show quoted text -
>
> > I am using MySQL 5.
> > The tables above are just an example. I was only providing familiar
> > looking tables here.
>
> > How can it be done by subqueries?
>
> > John
>
> Although there may be a simpler way, all I can think of is this:
>
> SELECT a.categoryid, count1, count2
> FROM categories a
> LEFT JOIN (
>
> SELECT c.categoryid, count( p1.name ) count1
> FROM categories c
> LEFT JOIN products1 p1 ON p1.categoryid = c.categoryid
> GROUP BY p1.categoryid
> )x ON a.categoryid = x.categoryid
> LEFT JOIN (
>
> SELECT c.categoryid, count( p2.name ) count2
> FROM categories c
> LEFT JOIN products2 p2 ON p2.categoryid = c.categoryid
> GROUP BY p2.categoryid
> )y ON y.categoryid = x.categoryid
> LIMIT 0 , 30

There's a tiny error there - although I don't think it affects the
result, however this is a little simpler:

SELECT a.categoryid, count1, count2
FROM categories a
LEFT JOIN (

SELECT categoryid, count( name ) count1
FROM products1
GROUP BY categoryid
)x ON x.categoryid = a.categoryid
LEFT JOIN (

SELECT categoryid, count( name ) count2
FROM products2
GROUP BY categoryid
)y ON y.categoryid = a.categoryid

In this instance 0=null

On Mar 2, 6:31 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 2, 6:22 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On Mar 2, 6:10 pm, "john7" <johnm...@fastermail.com> wrote:
>
> > > On Mar 2, 11:58 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > > On Mar 2, 5:16 pm, "john7" <johnm...@fastermail.com> wrote:
>
> > > > > I have three tables one for categories, one for products1 and another
> > > > > for products2
>
> > > > > 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
>
> > > > What version are you using?
> > > > This looks like a structural error. Why have you got two tables
> > > > holding the same kind of data? Further, what happens if a product
> > > > belongs to more than 1 category?
>
> > > > That said, this is easy if you use subqueries, but just trying to do
> > > > it with joins (I think) is going to be more tricky.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I am using MySQL 5.
> > > The tables above are just an example. I was only providing familiar
> > > looking tables here.
>
> > > How can it be done by subqueries?
>
> > > John
>
> > Although there may be a simpler way, all I can think of is this:
>
> > SELECT a.categoryid, count1, count2
> > FROM categories a
> > LEFT JOIN (
>
> > SELECT c.categoryid, count( p1.name ) count1
> > FROM categories c
> > LEFT JOIN products1 p1 ON p1.categoryid = c.categoryid
> > GROUP BY p1.categoryid
> > )x ON a.categoryid = x.categoryid
> > LEFT JOIN (
>
> > SELECT c.categoryid, count( p2.name ) count2
> > FROM categories c
> > LEFT JOIN products2 p2 ON p2.categoryid = c.categoryid
> > GROUP BY p2.categoryid
> > )y ON y.categoryid = x.categoryid
> > LIMIT 0 , 30
>
> There's a tiny error there - although I don't think it affects the
> result, however this is a little simpler:
>
> SELECT a.categoryid, count1, count2
> FROM categories a
> LEFT JOIN (
>
> SELECT categoryid, count( name ) count1
> FROM products1
> GROUP BY categoryid
> )x ON x.categoryid = a.categoryid
> LEFT JOIN (
>
> SELECT categoryid, count( name ) count2
> FROM products2
> GROUP BY categoryid
> )y ON y.categoryid = a.categoryid
>
> In this instance 0=null

Incidentally, despite the typos, you probably figured out that the
answer given at ...php was wrong (oh and try not to crosspost! - I
know, sometimes it can't be helped) but the suggestion given there
might give you an idea about how to turn a NULL into a 0.

On Mar 2, 12:31 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 2, 6:22 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
>
>
> > On Mar 2, 6:10 pm, "john7" <johnm...@fastermail.com> wrote:
>
> > > On Mar 2, 11:58 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > > On Mar 2, 5:16 pm, "john7" <johnm...@fastermail.com> wrote:
>
> > > > > I have three tables one for categories, one for products1 and another
> > > > > for products2
>
> > > > > 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
>
> > > > What version are you using?
> > > > This looks like a structural error. Why have you got two tables
> > > > holding the same kind of data? Further, what happens if a product
> > > > belongs to more than 1 category?
>
> > > > That said, this is easy if you use subqueries, but just trying to do
> > > > it with joins (I think) is going to be more tricky.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I am using MySQL 5.
> > > The tables above are just an example. I was only providing familiar
> > > looking tables here.
>
> > > How can it be done by subqueries?
>
> > > John
>
> > Although there may be a simpler way, all I can think of is this:
>
> > SELECT a.categoryid, count1, count2
> > FROM categories a
> > LEFT JOIN (
>
> > SELECT c.categoryid, count( p1.name ) count1
> > FROM categories c
> > LEFT JOIN products1 p1 ON p1.categoryid = c.categoryid
> > GROUP BY p1.categoryid
> > )x ON a.categoryid = x.categoryid
> > LEFT JOIN (
>
> > SELECT c.categoryid, count( p2.name ) count2
> > FROM categories c
> > LEFT JOIN products2 p2 ON p2.categoryid = c.categoryid
> > GROUP BY p2.categoryid
> > )y ON y.categoryid = x.categoryid
> > LIMIT 0 , 30
>
> There's a tiny error there - although I don't think it affects the
> result, however this is a little simpler:
>
> SELECT a.categoryid, count1, count2
> FROM categories a
> LEFT JOIN (
>
> SELECT categoryid, count( name ) count1
> FROM products1
> GROUP BY categoryid
> )x ON x.categoryid = a.categoryid
> LEFT JOIN (
>
> SELECT categoryid, count( name ) count2
> FROM products2
> GROUP BY categoryid
> )y ON y.categoryid = a.categoryid
>
> In this instance 0=null- Hide quoted text -
>
> - Show quoted text -

Thanks Strawberry, your above statement is working

On Fri, 02 Mar 2007 18:16:55 +0100, john7 <johnmark@fastermail.com> wrote:

> I have three tables one for categories, one for products1 and another
> for products2
>
> 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, do you want _distinct_ names, can the name be a NULL value that
should not be counted., or do you just want all matches?

Distinct names:
mysql> select c.id,COUNT(DISTINCT p1.name),COUNT(DISTINCT p2.name)
-> FROM categories c
-> LEFT JOIN products1 p1
-> on p1.catid = c.id
-> LEFT JOIN products2 p2
-> on p2.catid = c.id
-> group by c.id;

And just replace 'name' with 'id' if you want to count all matching rows..
Unless there's a very good reason to count names I can not see at the
moment?
--
Rik Wasmus

On Mar 2, 1:08 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 02 Mar 2007 18:16:55 +0100, john7 <johnm...@fastermail.com> wrote:
> > I have three tables one for categories, one for products1 and another
> > for products2
>
> > 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, do you want _distinct_ names, can the name be a NULL value that
> should not be counted., or do you just want all matches?
>
> Distinct names:
> mysql> select c.id,COUNT(DISTINCT p1.name),COUNT(DISTINCT p2.name)
> -> FROM categories c
> -> LEFT JOIN products1 p1
> -> on p1.catid = c.id
> -> LEFT JOIN products2 p2
> -> on p2.catid = c.id
> -> group by c.id;
>
> And just replace 'name' with 'id' if you want to count all matching rows.
> Unless there's a very good reason to count names I can not see at the
> moment?
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

Thank you Rik, thats even better

John