problems with a select

[Yo Mismo]

First at all, i`m spanish so please, apologize for my english
Hello to everybody, i`m a newby with mysql and i have a problem with a select
I`m making a database for a hostel
I have 2 tables for reservations, one is called rooms  and have 2 fields 
(id_room, tipo)and the other one is ocupation with 3 colums(id_reservation, 
date and id_room)
I`m using innodb database
I insert a register on the ocupation table for each day of the reservations, 
with the id_room and the id_reservation
I wanna mean, if u make a reservation for the 22-3-2007 to 24-3-2007 i will do 
that

  rooms table
id_reservationdateid_room
 00012007-03-22101
 00012007-03-23101
 00012007-03-24101

I know must there are a lot of better ways to do it, but i think is the 
simplest one
Now, my problem with the query is when i need to know for a especific date, 
witch rooms are avaliable
I wanna mean, if i try to check for the 22-03-2007 the query must return me all 
the rooms except the 101
Using SQL i can do it with a minus sentence, but the mysql version i`m 
using(5.0.24a) cant support it
Have anyone any idea?
Thanks, and please, forgive my english
;)

Re: problems with a select

[Raul Andres Duque]

I'm spanish speaker too .. but I write it in english for everyone can read 
it.


The version 5 implements subqueries. You can do something like:

SELECT id_room
FROM rooms
WHERE id_room NOT IN (SELECT id_room FROM reservations WHERE date = 
'22-03-07');


Regards,

RAUL DUQUE
Bogotá, Colombia

- Original Message - 
From: Yo Mismo [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, February 26, 2007 8:41 AM
Subject: problems with a select


First at all, i`m spanish so please, apologize for my english
Hello to everybody, i`m a newby with mysql and i have a problem with a 
select

I`m making a database for a hostel
I have 2 tables for reservations, one is called rooms  and have 2 fields 
(id_room, tipo)and the other one is ocupation with 3 colums(id_reservation, 
date and id_room)

I`m using innodb database
I insert a register on the ocupation table for each day of the reservations, 
with the id_room and the id_reservation
I wanna mean, if u make a reservation for the 22-3-2007 to 24-3-2007 i will 
do that


 rooms table
id_reservationdateid_room
00012007-03-22101
00012007-03-23101
00012007-03-24101

I know must there are a lot of better ways to do it, but i think is the 
simplest one
Now, my problem with the query is when i need to know for a especific date, 
witch rooms are avaliable
I wanna mean, if i try to check for the 22-03-2007 the query must return me 
all the rooms except the 101
Using SQL i can do it with a minus sentence, but the mysql version i`m 
using(5.0.24a) cant support it

Have anyone any idea?
Thanks, and please, forgive my english
;) 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]