Home Count(*) returns nothing is the table is empty with MySQL
Reply: 2

Count(*) returns nothing is the table is empty with MySQL

DamDam
1#
DamDam Published in 2018-01-13 04:26:33Z

I have these two tables:

`____Subscriptions`
|--------|--------------|-------------|
| SUB_Id | SUB_PlanName | SUB_HotelId |
|--------|--------------|-------------|
|      1 |         Tiny | 9           |
|--------|--------------|-------------|


`____Rooms`
|--------|--------------|-------------|
| ROO_Id | ROO_Name     | ROO_HotelId |
|--------|--------------|-------------|
|        No data        | 9           |
|--------|--------------|-------------|

I wanted to have the planName and the count of the rooms.

So my actual query is:

SELECT SUB_PlanName, 
COUNT(*) as ROO_Count 
FROM ___Rooms 
JOIN ___Subscriptions 
ON ___Rooms.ROO_Id = ___Subscriptions.SUB_HotelId
GROUP BY ___Subscriptions.SUB_PlanName;

This query works when I have a room in the table ___Rooms but do not return anything when the table ___Rooms is empty.

How can I change this behavior to return 0 for the count even if the table ___Rooms is empty?

AjahnCharles
2#
AjahnCharles Reply to 2018-01-13 04:56:11Z

If one table might not have data you need to OUTER JOIN pointing to the side that has data:

SELECT 
    SUB_PlanName, 
    COUNT(*) as ROO_Count 
FROM ___Subscriptions
LEFT OUTER JOIN ___Rooms ON ___Rooms.ROO_HotelId = ___Subscriptions.SUB_HotelId
GROUP BY ___Subscriptions.SUB_PlanName;

...or...

SELECT 
    SUB_PlanName, 
    COUNT(*) as ROO_Count 
FROM ___Rooms
RIGHT OUTER JOIN ___Subscriptions ON ___Rooms.ROO_HotelId = ___Subscriptions.SUB_HotelId
GROUP BY ___Subscriptions.SUB_PlanName;
Racil Hilan
3#
Racil Hilan Reply to 2018-01-13 05:00:33Z

I don't know why you have those weird underscores before your table names, but I will keep them.

The JOIN statement is a shortcut for INNER JOIN which returns records that have matching values in both tables. If you want to return all records from one table and the matching records from the other table, then you should use an outer join (LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN):

SELECT SUB_PlanName, COUNT(*) as ROO_Count 
FROM ___Rooms 
RIGHT JOIN ___Subscriptions ON ___Rooms.ROO_Id = ___Subscriptions.SUB_HotelId
GROUP BY ___Subscriptions.SUB_PlanName;

Also as Ajahn Charles pointed out, it's weird that you're joining the rooms table to the HotelId of the Subscriptions table. You should reconsider the naming of your fields.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307903 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO