สอบถามการ Query ข้อมูล Mysql แบบเปรียบทียบ 2 ตาราง

deknakhon · 03 พฤษภาคม 2011, 19:06:41

1. มีข้อมูลอยู่ 2 ตารา มีตารางหลัก คือ tb_Rooms เก็บข้อมูลห้องพักทั้งหมด
1.2 ตาราที่ 2 tb_roomassigns เก็บข้อมูลการใช้ห้องพัก
ปัญหาของผมคือ เมือผมทำการ Query ข้อมูล

โค้ด เลือก


SELECT a. *
FROM tb_rooms a
LEFT OUTER JOIN (

SELECT ROOMNO, DEPART
FROM tb_roomassigns
WHERE ARRIVE >= '2011-05-04'
AND DEPART <= '2011-05-10'
)b ON a.ROOMNO = b.ROOMNO
WHERE b.ROOMNO IS NULL

ก็ยังนำข้อมูลที่มีการใช้ในช่วงนั้นมาแสดงด้วย

โค้ด เลือก


# ตารางข้อมูลเก็บข้อมูลการใช้ห้องพัก
CREATE TABLE `tb_roomassigns` (
  `ID` int(11) NOT NULL auto_increment,
  `RESERVNO` decimal(10,0) NOT NULL,
  `ROOMNO` varchar(5) NOT NULL,
  `ARRIVE` date NOT NULL,
  `DEPART` date NOT NULL,
  `RMRATE` decimal(10,2) NOT NULL,
  `TOTALRATE` decimal(10,2) NOT NULL,
  `USERID` varchar(20) NOT NULL,
  `USERID_UPDATE` varchar(20) default NULL,
  `ASSIGN_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `ASSIGN_UPDATE` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ROOMNO` (`ROOMNO`,`ARRIVE`,`DEPART`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Assingn Room' AUTO_INCREMENT=3 ;

-- 
-- dump ตาราง `tb_roomassigns`
-- 

INSERT INTO `tb_roomassigns` VALUES (1, 11000029, 'A1', '2011-05-04', '2011-05-07', 4500.00, 13500.00, 'admin', NULL, '2011-05-03 18:38:25', '0000-00-00 00:00:00');
INSERT INTO `tb_roomassigns` VALUES (2, 11000030, 'A2', '2011-05-04', '2011-05-14', 4500.00, 45000.00, 'admin', NULL, '2011-05-03 18:44:06', '0000-00-00 00:00:00');

โค้ด เลือก


#ตารางข้อมูลห้องพักทั้งหมด
CREATE TABLE `tb_rooms` (
  `ID` int(11) NOT NULL auto_increment,
  `RMTYPE` varchar(6) NOT NULL,
  `ROOMNO` varchar(5) NOT NULL,
  `RMSTATUS` varchar(2) default NULL,
  `HKSTATUS` varchar(2) default NULL,
  `FOSTATUS` varchar(2) default NULL,
  `RMNIGHT` decimal(10,0) default NULL,
  `ROOMVIEW` varchar(40) default NULL,
  `NUM_OFBED` int(4) default '1',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `IXROOM` (`ROOMNO`),
  UNIQUE KEY `RMTYPE` (`RMTYPE`,`ROOMNO`),
  KEY `IDXFLOORROOM` (`ROOMNO`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=37 ;

-- 
-- dump ตาราง `tb_rooms`
-- 

INSERT INTO `tb_rooms` VALUES (1, 'SRBA', 'A1', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (2, 'SRBA', 'A2', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (3, 'SRBA', 'A3', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (4, 'SRBA', 'A4', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (5, 'SRBA', 'A5', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (6, 'SRBA', 'A6', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (7, 'SRBB', 'B1', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (8, 'SRBB', 'B2', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (9, 'SRBB', 'B3', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (10, 'SRBB', 'B4', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (11, 'SRBB', 'B5', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (12, 'SRBB', 'B6', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (13, 'SRBB', 'B7', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (14, 'SRBB', 'B8', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (15, 'SRBB', 'B9', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (16, 'SRBB', 'B10', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (17, 'SRBB', 'B11', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (18, 'SRBB', 'B12', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (19, 'SRBB', 'B13', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (20, 'SRBB', 'B14', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (21, 'SRBB', 'B15', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (22, 'SRBB', 'B16', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (23, 'SRBB', 'B17', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (24, 'SRBB', 'B18', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (25, 'SRBB', 'B19', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (26, 'SRBB', 'B20', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (27, 'SRBB', 'B21', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (28, 'SRBB', 'B22', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (29, 'SRBB', 'B23', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (30, 'SRBB', 'B24', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (31, 'SRBB', 'B25', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (32, 'SRBB', 'B26', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (33, 'SRBB', 'B27', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (34, 'SRBB', 'B28', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (35, 'SRBR', 'R1', NULL, NULL, NULL, NULL, NULL, 1);
INSERT INTO `tb_rooms` VALUES (36, 'SRBR', 'R2', NULL, NULL, NULL, NULL, NULL, 1);

โจทย์ของผมก็คือ ไม่ต้องการแสดงผลห้องพักที่มีการการใช้งานอยู่ในช่วงนั้น หรือว่าเป็นการตรวจสอบห้องว่างนั้นเองครับ
มืออาชีพโปรดช่วย มือสมัครเล่นอย่างผมด้วยครับ

dachin · 04 พฤษภาคม 2011, 10:20:34

ไม่รุเข้าใจถูกป่าว แต่ผมเข้าใจว่าต้องการแค่แสดง a.* อ๊ะป่าวอ่ะคับ ?

แบบนี้...

SELECT a.*
FROM tb_rooms a
WHERE a.ROOMNO NOT IN (SELECT ROOMNO FROM tb_roomassigns WHERE ARRIVE >= '2011-05-04'AND DEPART <= '2011-05-10');

PimonratC · 04 พฤษภาคม 2011, 10:33:48

ทำ flag ไว้ก็ได้นะคะ

พวกค่า boolean 1 0 ต่อท้ายตารางของห้องพัก

ห้องไหนมีคนเข้าพักแล้ว ก็เซตค่าเป็น 1 ห้องไหนว่า ก็เซตเป็น 0
ทีนี้ก็ดึงมาง่ายแล้ว

ตอนจองห้อง หรืออะไร ก็ใส่โค้ดให้มันคอยปรับเปลี่ยนสถานะของห้องเอาไว้ด้วย

น่าจะเวิร์กกว่านะ ^_^

deknakhon · 04 พฤษภาคม 2011, 10:44:29

อ้างถึงจาก: dachin ใน 04 พฤษภาคม 2011, 10:20:34
ไม่รุเข้าใจถูกป่าว แต่ผมเข้าใจว่าต้องการแค่แสดง a.* อ๊ะป่าวอ่ะคับ ?

แบบนี้...

SELECT a.*
FROM tb_rooms a
WHERE a.ROOMNO NOT IN (SELECT ROOMNO FROM tb_roomassigns WHERE ARRIVE >= '2011-05-04'AND DEPART <= '2011-05-10');

ใช้ครับเข้าใจถูกแล้วครับความหมายคือไม่ต้องการเอาห้องที่มีการใช้งานในตาราง tb_roomassigns ในช่วงเวลานั้น แล้วให้เอา ข้อมูลของตาราง tb_rooms ออกมาแสดงผล

INSERT INTO `tb_roomassigns` VALUES (2, 11000030, 'A2', '2011-05-04', '2011-05-14', 4500.00, 45000.00, 'admin', NULL, '2011-05-03 18:44:06', '0000-00-00 00:00:00');

แต่ตาม Query ที่ท่านตอบมาผมก็ลองดูแล้ว ยังมีการนำเอาห้องที่ A2 มาแสดงผลทั้งที่ A2 (04-14) มีการใช้งานในช่วงวันที่ 04-10 ด้วย

ขอบคุณสำหรับนำใจครับ
+1

deknakhon · 04 พฤษภาคม 2011, 10:47:57

อ้างถึงจาก: PimonratC ใน 04 พฤษภาคม 2011, 10:33:48
ทำ flag ไว้ก็ได้นะคะ

พวกค่า boolean 1 0 ต่อท้ายตารางของห้องพัก

ห้องไหนมีคนเข้าพักแล้ว ก็เซตค่าเป็น 1 ห้องไหนว่า ก็เซตเป็น 0
ทีนี้ก็ดึงมาง่ายแล้ว

ตอนจองห้อง หรืออะไร ก็ใส่โค้ดให้มันคอยปรับเปลี่ยนสถานะของห้องเอาไว้ด้วย

น่าจะเวิร์กกว่านะ ^_^

ถ้าเกิดมีการจองลวงหน้าเป็น หลายๆๆ เดือนพอตรวจสอบมาเจอกับ สถานะห้องที่ไม่ว่าง ก็เสียโอกาสในการจองไปเลยนะครับถ้าเป็นแบบนั้น
ผมเลยเอาวิธีนี้มาใช้ครับ

execter · 04 พฤษภาคม 2011, 10:52:58

ลอง INNER JOIN ดูครับ ตัวอย่าง

โค้ด เลือก


SELECT * FROM (tb_room tr INNER JOIN tr_roomassigns tra) ON (tra.ROOMNO = tr.ROOMNO) 
WHERE (tra.ARRIVE >= '2011-05-04'AND tra.DEPART <= '2011-05-10')

deknakhon · 04 พฤษภาคม 2011, 14:10:55

อ้างถึงจาก: execter ใน 04 พฤษภาคม 2011, 10:52:58
ลอง INNER JOIN ดูครับ ตัวอย่าง
โค้ด เลือก ขยาย
SELECT * FROM (tb_room tr INNER JOIN tr_roomassigns tra) ON (tra.ROOMNO = tr.ROOMNO) WHERE (tra.ARRIVE >= '2011-05-04'AND tra.DEPART <= '2011-05-10')

ทดลองแล้วออกมาเหมือนกันครับ แสดงผลแค่ห้องเดียวครับ
แต่ถ้าระบุวันที่ให้เลยวันที่ สุดท้านของห้อง A2 ข้อมูลจะแสดงผลออกมา 2 ห้องอย่างถูกต้องนะครับ

SELECT * FROM tb_rooms a INNER JOIN tb_roomassigns b ON (b.ROOMNO = a.ROOMNO)
WHERE b.ARRIVE >='2011-05-04' AND b.DEPART <= '2011-05-14'

kobkung · 06 พฤษภาคม 2011, 08:10:13

จำไม่ค่อยได้แฮะ จิ้มๆ navicat จะเขียนจำไม่ได้แล้ว โค้ดเก่าก็ไม่อยู่ใน notebook
เทสให้ก็ไม่ได้ win 7 รันโปรแกรมไม่ได้อีก

ไว้เข้า office วันเสาร์แล้วดูให้ถ้ายังไม่ได้

deknakhon · 10 พฤษภาคม 2011, 18:08:08

อ้างถึงจาก: kobkung ใน 06 พฤษภาคม 2011, 08:10:13
จำไม่ค่อยได้แฮะ จิ้มๆ navicat จะเขียนจำไม่ได้แล้ว โค้ดเก่าก็ไม่อยู่ใน notebook
เทสให้ก็ไม่ได้ win 7 รันโปรแกรมไม่ได้อีก ไว้เข้า office วันเสาร์แล้วดูให้ถ้ายังไม่ได้

ขอบพระคุณท่านลวงหน้านะครับ

shokarita · 11 พฤษภาคม 2011, 17:23:47

เปลี่ยนเป็น OR ดิครับ

shokarita · 11 พฤษภาคม 2011, 17:27:52

SELECT a.*
FROM tb_rooms a
WHERE a.ROOMNO NOT IN (SELECT ROOMNO FROM tb_roomassigns WHERE ARRIVE >= '2011-05-04' OR DEPART <= '2011-05-10');

kobkung · 11 พฤษภาคม 2011, 21:31:14

ยังไม่ได้อีกเรอะ ไม่ได้เทสให้เลย

ลอง select 2 ทีแบบข้างบนดู เหมือนจะคล้ายๆอย่างนั้น
ยังไม่ได้ก็มาลงไว้ละกัน จะได้ดูให้ (ตอนนี้ยังไม่ดูให้อยู่ดี

)

XXL · 12 พฤษภาคม 2011, 12:47:54

WHERE ARRIVE between('2011-05-04' and '2011-05-10') or
(ARRIVE < '2011-05-04' and DEPART between('2011-05-04' and '2011-05-10'))

deknakhon · 15 พฤษภาคม 2011, 00:53:25

อ้างถึงจาก: shokarita ใน 11 พฤษภาคม 2011, 17:27:52
SELECT a.*
FROM tb_rooms a
WHERE a.ROOMNO NOT IN (SELECT ROOMNO FROM tb_roomassigns WHERE ARRIVE >= '2011-05-04' OR DEPART <= '2011-05-10');

+1 เลยครับ อันนี้หน้าจะได้ผลครับ ไม่คอยจะกินแรมด้วย Query แบบนี้
ขอบคุณมากนะครับ

deknakhon · 15 พฤษภาคม 2011, 01:10:09

อ้างถึงจาก: XXL ใน 12 พฤษภาคม 2011, 12:47:54
WHERE ARRIVE between('2011-05-04' and '2011-05-10') or
(ARRIVE < '2011-05-04' and DEPART between('2011-05-04' and '2011-05-10'))

+1 ขอบคุณแนวทางของท่าน อันนี้ทำไปใช้ได้ผลจริงครับ แต่ผมไม่ได้เขียนแบบเดียวกับท่าน

โค้ด เลือก


SELECT a.* FROM tb_rooms a LEFT OUTER JOIN (select ROOMNO,DEPART,ARRIVE from tb_roomassigns where ARRIVE>='2011-05-06' and ARRIVE<='2011-05-26'
OR DEPART>='2011-05-06' and DEPART<='2011-05-26') b ON a.ROOMNO = b.ROOMNO WHERE b.ROOMNO is null

แสดงข้อมูลออกมาอย่างถูกต้อง

shokarita · 16 พฤษภาคม 2011, 00:59:42

ตามนั้นครับ เอาเร็วก็เอาให้ถูก เอาเก่งก็เอาให้ตรงหลักการคร้าบ 555