Professional Web Applications Themes

join for three tables with grouping - MySQL

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 ...

  1. #1

    Default 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

    john7 Guest

  2. #2

    Default Re: join for three tables with grouping

    On Mar 2, 5:16 pm, "john7" <com> wrote: 

    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.

    strawberry Guest

  3. #3

    Default Re: join for three tables with grouping

    On Mar 2, 11:58 am, "strawberry" <com> wrote: 








    >
    > 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 -[/ref]

    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


    john7 Guest

  4. #4

    Default Re: join for three tables with grouping

    On Mar 2, 6:10 pm, "john7" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > 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[/ref]

    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

    strawberry Guest

  5. #5

    Default Re: join for three tables with grouping

    On Mar 2, 6:22 pm, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > 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[/ref]

    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

    strawberry Guest

  6. #6

    Default Re: join for three tables with grouping

    On Mar 2, 6:31 pm, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > 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[/ref]

    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.

    strawberry Guest

  7. #7

    Default Re: join for three tables with grouping

    On Mar 2, 12:31 pm, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > 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 -[/ref]

    Thanks Strawberry, your above statement is working

    john7 Guest

  8. #8

    Default Re: join for three tables with grouping

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

    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
    Rik Guest

  9. #9

    Default Re: join for three tables with grouping

    On Mar 2, 1:08 pm, Rik <com> wrote: 







    >
    > 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 -[/ref]

    Thank you Rik, thats even better

    John

    john7 Guest

Similar Threads

  1. Listing join from tables...
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 5th, 09:02 PM
  2. New to Joines - Inner Join on 4 Tables
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 14th, 03:14 PM
  3. HELP.. Recordset JOIN tables
    By Addy_31 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 01:51 AM
  4. join on 3 tables for asp output
    By Mike in forum ASP Database
    Replies: 5
    Last Post: October 29th, 06:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139