return records with zero count

homer.favenir · #1 (**permalink**) 03-24-08, 04:49

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

r937 · #2 (**permalink**) 03-24-08, 08:49

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

koriba · #3 (**permalink**) 03-24-08, 09:25

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

homer.favenir · #4 (**permalink**) 03-24-08, 23:22

thanks guys....

Internet Services

Web Development

Digital Marketing

Consumer Tech