[web] SQL Question
Author
I don't now if this is the correct place, but since its for a web based application I thought I'd put it here.
The scenario is a hospital, you have wards which contain patients. I need to produce a query which shows all patients who have been in a specific ward, which is fairly easy. However it needs to display the ward details followed by the list of patients in the ward. The spec explicitly says that the ward details must only be shown once, which is what I'm struggling to do. I thought using 'GROUP BY WardNo' would produce the output required but that doesn't work either.
This is uni work so I'm not expecting the answer, just a nudge in the right direction would help.
cheers
Jon
[Edited by - Jonny_S on April 22, 2008 9:17:40 AM]
"ORDER BY WardNo" then for every row, whenever WardNo has changed (from one row to the next), display the details for the newly encountered ward.
Author
Ahhh we're using Oracle so I'd create views.... excellent! Thanks for the help!
[edit]
Just to clarify....this can't be done with a single query can it?
[Edited by - Jonny_S on April 22, 2008 12:07:03 PM]
[edit]
Just to clarify....this can't be done with a single query can it?
[Edited by - Jonny_S on April 22, 2008 12:07:03 PM]
It can be done with a single query. Use a UNION SELECT. Since this is uni work I won't give you an example. You should be able to figure it out from here :-)
Author
How interesting, I tried to use union earlier, but oracle wasn't liking it. I'll try again tomorrow, thanks for your help :).
[edit]
Maybe this is just because I'm new to SQL, but isn't using UNION just going to list all of the Wards then all of the patients?
i.e.
instead of:
Also I forgot to mention, PatientName is split into firstname/surname so surely a union can't be used (Incorrect number of fields)? I'm assuming I'm missing something here, but I'll find out tomorrow no doubt.
[Edited by - Jonny_S on April 22, 2008 5:34:49 PM]
[edit]
Maybe this is just because I'm new to SQL, but isn't using UNION just going to list all of the Wards then all of the patients?
i.e.
WardNo | WardName 1 | Ward1 2 | Ward2__________________ 1 | Patient1 2 | Patient2instead of:
WardNo | WardName 1 | Ward1 1 | Patient1__________________ 2 | Ward2 2 | Patient2Also I forgot to mention, PatientName is split into firstname/surname so surely a union can't be used (Incorrect number of fields)? I'm assuming I'm missing something here, but I'll find out tomorrow no doubt.
[Edited by - Jonny_S on April 22, 2008 5:34:49 PM]
Quote:Maybe this is just because I'm new to SQL, but isn't using UNION just going to list all of the Wards then all of the patients?
Yes. Or you can restrict it to one ward using a WHERE clause. In your original post you said you wanted a list of patients + ward details for a specific (i.e. one) ward. You didn't say all wards. That's a lot harder and something which I would defer to application logic, not implement in database queries.
Author
Ah I'm sorry, I did say a specific ward my bad. My initial gut reaction was to handle the formatting in the application logic, what stopped me was the fact that this module primary focus was databases and SQL (the web stuff is tacked on). However, since the module is only an intro to SQL I'm going to assume they are not expecting anything more complex than a few JOIN queries.
Sorry for wasting your time! Had I been more specific in the beginning I may not have wasted an entire day myself.
Sorry for wasting your time! Had I been more specific in the beginning I may not have wasted an entire day myself.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
