SQL query

[Guest_imported]

Could some one me on this one please.

two tables, one called Ward and the other called Patient.
Column headings for Ward are ward_no,ward_name and number_of_beds.
Column headings for Patient are patient_id,patient_name,consultant_no and ward_no.

Question:Give the name of each ward that is not full (the number of beds is greater than the number of patients in that ward)

I am looking for the SQL query.

Cheers

Bill

 

[bperry]

This should work in SQL Server, and prob. Oracle and Sybase. Doubt that it work in Access.
---------------------------------------
Select * from
)
Select
ward_no, ward_name, number_beds,
(Select Count(ward_no) from Patient p
where p.ward_no = ward.ward_no) as occupied
From Ward
) dt
Where occupied < number_beds
---------------------------------

Hopefully no syntax errorz.

 

[Guest_imported]

Cheers mate for your help

but still getting syntax error

 

[bperry]

ooops. Yes, there were some syntax errors there. (I hadn't had an opportunity to test it.

Here's two versions that do work on my system, if you care to try them.
------------------------------
Select * from
(
Select
ward_no, ward_name, number_of_beds,
(Select Count(ward_no) from Patient p
where p.ward_no = ward.ward_no) as occupied
From Ward
) dt
Where occupied < number_of_beds
--------------------------
Select w.ward_name, count(p.ward_no) as occupied
from ward w inner join patient p
ON w.ward_no = p.ward_no
group by w.ward_name
Having count(p.ward_no) < MAX(w.number_of_beds)
-------------------------------

 

[Guest_imported]

second statement is perfect

cheers mate