SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict GeertDD's Avatar
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    334
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [SQL] Empty categories

    Given
    Table called "products" (id, name, price, ...)
    Table called "categories" (id, name, description, ...)
    Table called "categories_products" containing links for a product to its category/ies (id_product, id_category)

    What I need
    A list of all categories and the amount of products each category contains, including --and this is the problem-- empty categories!

    Solution?
    Code:
    SELECT c.id, c.name, COUNT(cp.id_product) AS total_products_in_cat
    FROM categories c
    LEFT JOIN categories_products cp ON c.id = cp.id_category
    GROUP BY cp.id_category
    This query only works partially. I do get the total number of products for categories that contain 1 or more products.

    However, this query does not return all empty categories with a product count of 0. Strange enough, it returns only one empty category. I have no idea why.

  2. #2
    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)
    Code:
    select c.id
         , c.name
         , count(cp.id_category) as total_products_in_cat
      from categories c
    left outer
      join categories_products cp 
        on c.id = cp.id_category
    group 
        by c.id
         , c.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict GeertDD's Avatar
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    334
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, that works! Now I finally understand what I was doing wrong. Thanks a lot, r937!

    One more question: is there any reason you use "left outer join" instead of just "left join"? Both return the same in this case.

  4. #4
    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)
    yes, i always use the word OUTER because sometimes it is required -- i forget where, exactly, because i always use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict GeertDD's Avatar
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    334
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I thought I could continue on my own now, but I'm already facing a new problem.

    Some products are not activated and should not be counted for total_products_in_cat.

    This doesn't work:
    Code:
    select c.id
         , c.name
         , count(cp.id_category) as total_products_in_cat
      from categories c
    left outer
      join categories_products cp 
        on c.id = cp.id_category
    left outer
      join products p 
        on p.id = cp.id_product
    where p.activated = 1
    group 
        by c.id
         , c.name

  6. #6
    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)
    Code:
    select c.id
         , c.name
         , count(cp.id_category) as total_products_in_cat
      from categories c
    left outer
      join categories_products cp 
        on c.id = cp.id_category
    left outer
      join products p 
        on cp.id_product = p.id 
       and p.activated = 1
    group 
        by c.id
         , c.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict GeertDD's Avatar
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    334
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this but it still returns the total product count regardless of whether they're activated or not.

  8. #8
    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)
    change count(cp.id_category) to count(p.id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict GeertDD's Avatar
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    334
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that does the job! It's time for me to sharpen up my SQL-skills, I guess. Thanks again!


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
  •