I hardly find any reason to use the old comma-join and you might never achieve your desired result if you stick to it. Try this query:
SELECT r.roomno,
MAX(r.seatNum)-SUM(CASE WHEN p.roomno IS NULL THEN 0 WHEN p.roomno IS NOT NULL AND p.TimeLeave IS NOT NULL THEN 0 ELSE 1 END) AS AvailableSeatNum,
SUM(CASE WHEN p.roomno IS NULL THEN 0 WHEN p.roomno IS NOT NULL AND p.TimeLeave IS NOT NULL THEN 0 ELSE 1 END) AS NotAvailable
FROM room AS r
LEFT JOIN people AS p
ON r.roomNo=p.roomNo
GROUP BY r.roomno;
My mind is actually not in excellent shape right now so this is what I can come up. I'll attempt to break it down and explain to you one by one but I may miss a thing or two to mention. Anyways:
- Change from comma-join to use
JOIN; and in this particular problem, use LEFT JOIN. I'm talking about your query part here .. FROM room,people ...
- Assign aliases to your table
.. room AS r .... These are just for readability purposes and especially useful for long table names. Once you've assign and alias, you can use it throughout the query. Just don't use duplicate aliases.
- According to your example data, it seems like the value for
seatNum is fixed so I used MAX() to cater sql_mode=only_full_group_by. There are other methods too like ANY_VALUE() but I'll just use MAX() for this example. However, I assume that you're sql_mode=only_full_group_by is turned off but if it's not, consider these methods.
In the SELECT, I've made a bunch of CASE expression condition to get the calculation according to your desired result. I attempt to make it work without using sub-query but I personally would prefer to use a sub-query at least; it's easier for me to read. What I mean is:
SELECT rp.roomno,
MAX(rp.seatnum)-SUM(rp.cval) AS AvailableSeatNum,
SUM(rp.cval) AS NotAvailable
FROM
(SELECT r.roomno, r.seatNum, p.id, p.TimeLeave,
CASE WHEN p.roomno IS NULL THEN 0
WHEN p.roomno IS NOT NULL AND p.TimeLeave IS NOT NULL THEN 0
ELSE 1 END AS cval
FROM room AS r
LEFT JOIN people AS p
ON r.roomNo=p.roomNo) rp
GROUP BY rp.roomno;
With a sub-query, I only need to do CASE expression once and then later just refer to it on the outer query. Let's focus on the CASE:
CASE WHEN p.id IS NULL THEN 0
WHEN p.id IS NOT NULL AND p.TimeLeave IS NOT NULL THEN 0
ELSE 1 END AS cval
WHEN p.roomno IS NULL THEN 0 is checking the result of LEFT JOIN between room and people table. Since, people table don't have roomno=3, it will return p.roomno as NULL, hence CASE will change that to 0.
WHEN p.roomno IS NOT NULL AND p.TimeLeave IS NOT NULL THEN 0 checks if the LEFT JOIN have matches of roomno for both table, it will return value for p.roomno and if p.TimeLeave have value instead of NULL, means it's occupied.
and ELSE 1 is just telling that if any of the condition above is not met, return 1. With the values returned between 0 and 1, you'll just have to do SUM() instead of COUNT() at the outer query (since COUNT() will count how many rows).
I hope the explanation is easy to understand. Here is a fiddle for reference:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=17b88d7de91f4135f6bc1a795ff2a86e
