Hotel reservation system Sql Query?

Question

I want to create a hotel reservation system. For this system; the database is also used for another program ... But I have a problem: before booking, I want to see how many rooms are available for my order.

My table creates SQL queries

CREATE TABLE oteldb.oda ( oda_id INT (11) NOT NULL auto_increment, oda_tip_id INT (11) DEFAULT NULL, oda_adi VARCHAR (20) DEFAULT NULL, PRIMARY KEY (oda_id) ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci; 

---

 CREATE TABLE oteldb.tip ( tip_id INT (11) NOT NULL auto_increment, tip_adi VARCHAR (20) DEFAULT NULL, PRIMARY KEY (tip_id) ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT = FIXED; 

---

 CREATE TABLE oteldb.rezervasyon ( rezervasyon_id INT (11) NOT NULL auto_increment, rezervasyon_gt DATE DEFAULT NULL, rezervasyon_ct DATE DEFAULT NULL, rezervasyon_oda_id INT (11) DEFAULT NULL, PRIMARY KEY (rezervasyon_id) ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET utf8 COLLATE utf8_general_ci; 

---

I try this but not work

 SELECT * FROM oteldb.tip WHERE IN tip.tip_id (SELECT oteldb.oda.oda_tip_id FROM oteldb.oda WHERE IN oda.oda_id note (SELECT oteldb.rezervasyon.rezervasyon_oda_id FROM oteldb.rezervasyon WHERE "2012-01-03" BETWEEN AND rezervasyon_ct rezervasyon_gt AND "2012-01-22" AND BETWEEN rezervasyon_gt rezervasyon_ct)) 

---

thanks now ...

Answer 1

Assuming the available rooms are those that have not yet been reserved at any time during the request period, and that rezervasyon_gt and rezervasyon_ct are the start and end dates of the reservation, respectively, try:

 select t.tip_adi, count(oda.oda_tip_id) from oteldb.tip t left join (select oda_tip_id from oteldb.oda o where not exists (select null from oteldb.rezervasyon r where r.rezervasyon_oda_id = o.oda_id and r.rezervasyon_gt <= '2012-01-22' and '2012-01-03' <= r.rezervasyon_ct) ) oda on oda.oda_tip_id = t.tip_id group by t.tip_adi 

Answer 2

 select RoomType.tip_adi, sum( if( Rooms.oda_id = BookedRooms.rezervasyon_oda_id, 0, 1 ) as AvailableCount from oteldb.oda Rooms LEFT JOIN ( select distinct res.rezervasyon_oda_id from oteldb.rezervasyo res where res.rezervasyon_gt between '2012-01-22' and '2012-01-03' OR res.rezervasyon_ct between '2012-01-22' and '2012-01-03' ) BookedRooms on Rooms.oda_id = BookedRooms.rezervasyon_oda_id JOIN oteldb.tip RoomType on Rooms.oda_tip_id = RoomType.tip_id