Can somebody pls give me some advise?? pls...I'm really poor in SQL When new patients are about to be admitted, the hospital needs a listing showing how many beds are currently unoccupied in each ward. PROMPT ---------------------------------------------------------------------------------------; PROMPT BEDS currently UNOCCUPIED in each WARD select bedNumber from bed where bedNumber NOT IN (select a.bedNumber from admission a where a.dischargeDate <= SYSDATE) or don't know which one is right, or both wrong............ PROMPT ---------------------------------------------------------------------------------------; PROMPT BEDS currently UNOCCUPIED in each WARD SELECT bedNumber FROM bed where bedNumber NOT IN (select bedNumber from admission); the following is the oringal database: CREATE TABLE bed( wardNumber char(2), bedNumber number, constraint bed_pk primary key (wardNumber, bedNumber)); CREATE TABLE admission( patientID char(4), admissionDate date, dischargeDate date, reason char(1), wardNumber char(2), bedNumber number, constraint admission_pk primary key (patientID, admissionDate), constraint admission_bed_fk foreign key (wardNumber, bedNumber) references bed (wardNumber, bedNumber));

Hi tabobomber,    Hope helpful to you !!

select  wardNumber,
        bedNumber
from    bed
where   (wardNumber, bedNumber) not in (select  a.wardNumber,
                                                a.bedNumber
                                        from    admission a
                                        where  (a.dischargeDate is null
                                            or  a.dischargeDate > sysdate))

---------------------------------- 小弟才疏學淺，若有謬誤尚請不吝指教 ----------------------------------

------
Fishman

thx a lot! reallY