I have a table called patients:
create table patients
(consultant_no char(3) not null,
ward_no char(2) not null)
How do I write a composite SQL query that gives the ward_no and consultant_no where the consultant is responsible for ten or more patients in that ward? I know that a simple SQL query to generate the above question is:
select ward_no, consultant_no from patients
group by consultant_no, ward_no
having count(consultant_no) >= 10
I expect a composite SQL query would use subquery to do so.
Your simple query is wrong, unless a single consultant can be associated with the same ward more than once. If you GROUP BY consultant_no and ward_no, each combination of values should occur only once, hence the HAVING condition will never be satisfied.
This leads me to believe that there's been an error or typo in the way you've presented the question. There's probably a patient number in the table as well:
create table patients ( consultant_no char(3) not null , patient_no char(6) not null , ward_no char(2) not null )
Now we can see that your original query makes sense. If you GROUP BY consultant_no and ward_no, each combination of values could occur many times, once per patient.
But now another problem crops up. Your simple query is the solution to your original question:
... the ward_no and consultant_no where the consultant is responsible for ten or more patients in that ward
So again, let's assume you meant to ask something else. Let's assume you meant to ask:
... the ward_no, consultant_no, and patient_no where the consultant is responsible for ten or more patients in that ward
Now we need a more complex query:
select ward_no , consultant_no , patient_no from patients as X where consultant_no in ( select consultant_no from patients where ward_no = X.ward_no group by consultant_no having count(consultant_no) >= 10 )
Here the subquery is correlated, and selects only consultants in the same ward with 10 or more patients.
Moral of the story: if you are going to submit a question to Ask the Expert, make sure it makes sense, and does not require guessing or fudging of the question as submitted. Ordinarily we just reject questions like that, but this one was accepted because it allowed me to illustrate why so many questions are rejected.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.