Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Talking Unanswered: return records with zero count

    hi,
    i have a query but it displays only those records with count, and those with no count doesnt display

    here is my query

    SELECT
    tbl_menu.menu_date,
    tbl_menu.concessionaire,
    tbl_menu.picture,
    tbl_menu.meal,
    tbl_menu.`type`,
    tbl_menu.price,
    (tbl_menu.availability - count(tbl_order.`order`)) as 'Available'
    FROM
    tbl_menu
    inner join tbl_order on (tbl_menu.meal = tbl_order.`order`)
    Group by
    tbl_order.`order`

    i have a count(tbl_order.`order`) = 0 but it does not appear.
    this query only displays the records when count is not null
    what i need is all records should disply even if the count is not null or null.

    pls advice.
    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT tbl_menu.menu_date
         , tbl_menu.concessionaire
         , tbl_menu.picture
         , tbl_menu.meal
         , tbl_menu.`type`
         , tbl_menu.price
         , tbl_menu.availability - COALESCE(daCount,0) as Available
      FROM tbl_menu
    LEFT OUTER
      JOIN ( SELECT `order`
                  , count(*) AS daCount
               FROM tbl_order 
             GROUP
                 BY `order` ) AS orders
        ON orders.`order` = tbl_menu.meal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Location
    Windhoek Namibia
    Posts
    13
    in your query you could also subst .<<count(tbl_order.`order`)>> with <<(count(tbl_order.`order`)+1)>> and then know that in your other code. Nasty but works
    Wayne Philip - openaxon.com

  4. #4
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks guys....
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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