SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Join & Count

  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join & Count

    Sorry, i cant get my head around this one...

    Two tables - products, designers. The products.designer refers to the designers.desid

    I want to join the two tables on the products.designer and count the number of products by the designer.

    Simply i have;
    Code:
    SELECT product.designer, designers.desid, designers.name
    FROM designers LEFT JOIN products on designers.desid=products.designer
    .....so, what would be the most effective way to query the products (at the moment there are 500 + and counting) to ouput somthing like;

    HTML Code:
    designer name | designer id | no. products
    designer1          | 1              | (120)
    designer2          | 2              | (50)
    designer3          | 3              | (140) etc etc
    Thanks in advance, just my head hurts this morning!

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Code SQL:
    SELECT
        t1.desid AS designer_id
        ,t1.name AS designer_name
        ,COALESCE(COUNT(t2.id),0) AS designer_products
     FROM
        designers AS t1
     LEFT
     JOIN
        products AS t2
       ON
         t1.desid = t2.designer
    GROUP
       BY
        t1.desid

    t2.id is meant to be the products primary key. However, I didn't see that in your example.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    COUNT() is one of those neat aggregate functions that will return 0 if there is nothing to count, so you don't have to COALESCE it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •