Mysql joining tables question

Question

I have a tables rooms, apartments and hotel. Now its easy for me to get all free rooms, name of hotel address or same thing for apartments by using separate queries ex :

Get all free rooms :

SELECT hotel.name, count(*) AS num_of_free_rooms
FROM hotel, rooms
WHERE rooms.occupied !=0
  AND hotel.hotel_id = rooms.room_hotel
GROUP BY hotel.hotel_id

Get all free apartments :

SELECT hotel.name, count(*) AS num_of_free_ap
FROM hotel, apartments
WHERE apartments.occupied_ap !=0
  AND hotel.hotel_id = apartments.apartment_hotel
GROUP BY hotel.hotel_id

How could I get results like this :

Name of a hotel | Number of free rooms other than 0 | Number of apartments 0 or any other

Should I organize my data differently, like adding in the table rooms field type 1 for rooms, 2 for apartments or do it with separate tables. I'm open to any suggestions in order to get results that I need.

Answer 1

You can use this until someone comes up with a (more) efficient solution :)

SELECT tmp.name, tmp.num_of_free_rooms, num_of_free_ap
FROM
  (SELECT hotel.name, count(*) AS num_of_free_rooms
  FROM hotel, rooms
  WHERE rooms.occupied !=0
    AND hotel.hotel_id = rooms.room_hotel
  GROUP BY hotel.hotel_id) AS tmp
LEFT JOIN
  (SELECT hotel.name, count(*) AS num_of_free_ap
  FROM hotel, apartments
  WHERE apartments.occupied_ap !=0
    AND hotel.hotel_id = apartments.apartment_hotel
  GROUP BY hotel.hotel_id) AS tmp2
ON tmp1.hotel.name = tmp2.hotel_name
WHERE tmp.num_of_free_rooms > 0

Answer 2

If rooms and apartments share many attributes, it's worth putting them both in the same table (with a flag) just to simplify queries like the one you mentioned. You can put the special attributes in room_details and apt_details tables and only refer to them when needed.

Answer 3

If you have rooms and apartments in separate tables, and you try to count them in a single query, you'll produce what's called a Cartesian product between rooms and apartments. The effect is that your counts of each will be much higher than reality (they'll be multiplied together).

Keep in mind you don't have to solve every problem in SQL with a single query! Running separate queries is simpler and often runs faster than a single overly-complex query. So don't overlook the option of simply running two queries.

That said, if I were designing this database, I would make rooms and apartments part of the same table, and use an attribute to distinguish between these two types, just as you describe. Unless you need a lot of columns specific to one type or the other.

For more options, see my answer in "Product table, many kinds of product, each product has many parameters."