BorlandTalk.com

Jeff · Guest

I have 2 tables...
one called rooms...
room#,bed which is a list of all existing rooms

another called residents
firstname, lastname, room#,bed, and so on.

If i have so many residents using room# and bed... how can I get a list of
room#,bed not being used yet?

I am currently doing this...
select * from room
where NOT((room="1" and Bed="A") or (Room="1" and Bed="B") ....... ) and so
on
Order by Room,Bed

Altho...v4.1 apparently supports this... I couldn't make it work either.
//dm.Rooms.Sql.Add('SELECT room.room,room.bed FROM room');
//dm.Rooms.Sql.Add('where (room.room,room.bed) NOT IN (SELECT room,bed
from Resident)');

what's the best way to do this with 3.23.58?

thanks

Jeff

Jeff · Guest

I figured this out... with mysql v4.1.3... i can use the following

// select distinct room.room,room.bed,room.section from room,resident
// where NOT EXISTS(Select * from resident where room.room=resident.roomm
and room.bed=resident.bed)

and this seems to work. How would i duplicate this in 3.23.58 without using
sub-queries?
thanks..

Jeff

"Jeff" <jlabute (AT) shaw (DOT) ca> wrote