Count limit to a specific value defined in another table

Question

This is the 2 tables that I have

unit:

unit name    |    no. of beds
-----------------------------
unit 1       |         4
unit 2       |         3

worker:

name         |    unit name
-----------------------------
worker 1     |     unit 1
worker 2     |     unit 1
worker 3     |     unit 1
worker 4     |     unit 1
worker 5     |     unit 1
worker 6     |     unit 2

I need to count the no of beds available for each unit, but limit each count to the respective unit's number of beds. I.e. if in table unit, unit 1 defined to have 4 beds, but in table worker it has 5 workers tagged to unit 1, it should only return the count as 4.

This is what I have now but it returns total count, disregarding the no. of beds defined for each room.

The result I want is to return "2" for the # of available beds. How can I incorporate that into my query?

$sql = "SELECT COUNT(*) AS 'count_worker' FROM worker WHERE (`unit_id` IS NOT NULL and `unit_id` <> '')";
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);

$sql2 = "SELECT SUM(no_bed) AS 'count_vacancy' FROM unit WHERE `active` = 'yes'";
$rs2 = CustomQuery($sql2);
$data2 = db_fetch_array($rs2);

$vacancy = $data2["count_vacancy"] - $data["count_worker"];
if ($vacancy >0)
{
echo "# of beds available: " . $vacancy;
}
else 
{
echo "# of beds available: 0";
}

Answer 1

http://sqlfiddle.com/#!9/674e4/1

SELECT 
  w.unit_name, 
  if(w.cnt<=u.no_of_beds,w.cnt,u.no_of_beds)
FROM (
  SELECT count(*) cnt, unit_name
  FROM worker
  GROUP BY unit_name) w
LEFT JOIN unit u
ON w.unit_name = u.unit_name

and to get # of available beds http://sqlfiddle.com/#!9/88734/2

SELECT 
  u.unit_name, 
  if(w.cnt<=u.no_of_beds,u.no_of_beds-w.cnt, IF(w.cnt IS NULL,u.no_of_beds,0)) beds_available
FROM unit u
LEFT JOIN (
  SELECT count(*) cnt, unit_name
  FROM worker
  GROUP BY unit_name) w
ON w.unit_name = u.unit_name;