mysql count not returning zero

I have this query:

SELECT COUNT( tbl_workstation.workstation_id ) AS AVAILABLE, tbl_lab.room_no,
tbl_lab.capacity
FROM tbl_workstation, tbl_lab
WHERE tbl_workstation.logged_on =0
AND tbl_workstation.startup =1
AND tbl_workstation.lab_name = tbl_lab.lab_name
G开发者_高级运维ROUP BY tbl_lab.lab_name

I get the correct counts for each of the labs - However where the count is 0 - Nothing is returned. I need to return the rows where the count is zero also. I have looked at doing a left join but I get all sorts of errors. Anything from wrong result sets to ambiguous column errors. What am doing wrong?

If you want to get all labs, even those with 0 workstations, use the labs table as your primary table and outer join to the workstations table.

Something like this should work:

SELECT COUNT( tbl_workstation.workstation_id ) AS AVAILABLE, tbl_lab.room_no,
tbl_lab.capacity
FROM tbl_lab
LEFT OUTER JOIN tbl_workstation ON tbl_workstation.logged_on =0 
  AND tbl_workstation.startup =1 
  AND tbl_workstation.lab_name = tbl_lab.lab_name
GROUP BY tbl_lab.lab_name

The problem lies in your AND tbl_workstation.lab_name = tbl_lab.lab_name. This query only return resultat where the lab has workstations in it, that's why you don't get any results from an empty lab.

And you are right, you need to use a LEFT JOIN

SELECT COUNT( tbl_workstation.workstation_id ) AS AVAILABLE, tbl_lab.room_no,
tbl_lab.capacity
FROM tbl_workstation
LEFT OUTER JOIN tbl_lab ON bl_workstation.lab_name = tbl_lab.lab_name
WHERE tbl_workstation.logged_on =0
AND tbl_workstation.startup =1
GROUP BY tbl_lab.lab_name

This should be good.

I would suggest something like this:

SELECT
    tbl_lab.lab_name
    , tbl_lab.room_no
    , tbl_lab.capacity
    , SUM (
            CASE WHEN tbl_workstation.logged_on = 1 OR tbl_workstation.startup = 0 THEN 0 ELSE 1 END
    ) as AVAILABLE
FROM tbl_lab, tbl_workstation
WHERE
    tbl_workstation.lab_name = tbl_lab.lab_name
GROUP BY tbl_lab.lab_name, tbl_lab.room_no, tbl_lab.capacity
ORDER BY tbl_lab.lab_name, tbl_lab.room_no

I assume that tbl_workstation.startup means that the workstation is turned on and available and that workstations that are not started up are not available. I.e. a workstation must not be logged on and must be started up to be available.