使用 Mysql 检索特定元组 [英] Retrieving specific tuples using Mysql

问题描述

我在检索特定元组时遇到一些问题.我实际上是一个试图建立房间管理系统的学生.我有两张桌子:

I have some problems retrieving specific tuples. I am actually a student trying to build a Room management system. I have two tables:

Room(roomID,hotelname,rate) 

和

Reservation(resID,arriveDate,departDate,roomID).

我不确定如何检索 2 个特定日期之间的可用房间.这是我使用的查询.

I am not sure how to retrieve the rooms that are available between 2 specific dates. This was the query that i used.

SELECT Room.roomID,hotelname,rate 
  FROM Room 
  LEFT JOIN Reservation 
    on (     Room.roomID=Reservation.resID 
         and arriveDate >='2010-02-16' 
         and departDate <='2010-02-20'
       ) 
 GROUP BY roomID,hotelname,rate 
HAVING count(*)=0;'

但它返回一个空集.任何人都可以告诉我我在做什么错误吗??

but it returns an empty set. Can any1 be kind enough to tell me what mistake i am doing??

推荐答案

我猜 Room.roomID=Reservation.resID 应该是 Room.roomID=Reservation.roomID.

您可以使用子选择尝试不同的方法:

You could try a different approach with a subselect:

SELECT roomID,hotelname,rate 
FROM Room 
WHERE roomID NOT IN (SELECT roomID FROM Reservation WHERE arriveDate >='2010-02-16' and departDate <='2010-02-20')

这篇关于使用 Mysql 检索特定元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！