# [web] SQL Question

This topic is 3588 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

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]

##### Share on other sites
"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.

##### Share on other sites
Thanks for the help, but I have no idea how to do that in purely in SQL?

##### Share on other sites
If you can use temp tables, generate the list of patients by Ward first.

##### Share on other sites
Ahhh we're using Oracle so I'd create views.... excellent! Thanks for the help!

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]

##### Share on other sites
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 :-)

##### Share on other sites
How interesting, I tried to use union earlier, but oracle wasn't liking it. I'll try again tomorrow, thanks for your help :).

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    | Patient2

WardNo | WardName  1    | Ward1  1    | Patient1__________________  2    | Ward2  2    | Patient2

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]

##### Share on other sites
That diagram would have helped a lot in your original post.

##### Share on other sites
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.

##### Share on other sites
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.

##### Share on other sites
Quote:
 Original post by Jonny_SHowever 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.

Generally speaking, if this has to be done in one fairly simple query, this sounds impossible to me. If a ward has had 2 or more patients in it, then a join will yield at least 2 rows, one for each patient. But you need to show the details for the ward just once, yet they'll appear and will be the same on each row.

One possible way is to collapse the several patient rows for each ward into one via an aggregate function. In MySQL, GROUP_CONCAT may be able to do this. Though I'm guessing that is outside the scope of your task, perhaps.

##### Share on other sites
Quote:
 One possible way is to collapse the several patient rows for each ward into one via an aggregate function. In MySQL, GROUP_CONCAT may be able to do this. Though I'm guessing that is outside the scope of your task, perhaps.

Yes, as I said this is an introduction to SQL, we have been given a workbook to go through but that doesn't go beyond the scope of JOIN's and grouping. So I'm assuming the solution to the task must be in the workbook and therefore I must be over thinking the solution...

The only way I can see going about it is writing each query for each ward individually i.e.:
REM output childrens ward infoSELECT WardNo, WardName FROM Ward WHERE WardName = 'Childrens'REM list patients in childrens wardSELECT PatientNo, FirstName, Surname FROM Patient WHERE blah blah

Then do that for evey ward, however this poses a problem if a new ward is added, is there any suggestions as to how this could be automated further? (in SQL I mean, obviously using PHP I could automate this fine).

This task has really thrown me a curve ball, all the other sections of the task were REALLY easy in comparison to this....As I said I must be over thinking it...

##### Share on other sites
I suspect that the Web based technology stack should be responsible for the header/detail aspect of the report output. The SQL aspect to the answer is that you must utilize the ORDER BY clause.

##### Share on other sites
Ok, after speaking to my lecturer it turns out the report must be generated in oracle.....so this must be done purely in SQL.

I'm baffled now...

It seems the correct format can be produced using PL/SQL, I'll give that a go.

[edit2]

Just incase anyone was interested, the answer ended up being icnredibly simple. You use the iSQL*Plus command
BREAK ON

BREAK ON WARDNOSELECT DISTINCT Ward.WardNo, WardName, FirstName FROM Ward INNER JOINWardPatient ON Ward.WardNo = WardPatient.WardNo INNER JOIN Patient ON Patient.PatientNo = WardPatient.PatientNo ORDER BY Ward.WardNo;

I had tried that first but I didn't get it too work....now it does.

Thanks for all the help.

[Edited by - Jonny_S on April 24, 2008 7:37:06 AM]

##### Share on other sites
Ah, I'm guessing that's not a standard SQL command.

##### Share on other sites
No its an oracle iSQL*Plus command, I spent 2 days trying to find a complex solution when all it required was one line of code.

I am thoroughly embarrassed...

##### Share on other sites
Not too embarrassing when you consider that most of the sources you could go to wouldn't have ever known of that command.