Q
Problem solve Get help with specific problems with your technologies, process and projects.

Clarity in stating the question

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.


This was last published in October 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close