Sign in to follow this  
Jonny_S

[web] SQL Question

Recommended Posts

Jonny_S    149
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 this post


Link to post
Share on other sites
Jonny_S    149
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]

Share this post


Link to post
Share on other sites
Sander    1332
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 this post


Link to post
Share on other sites
Jonny_S    149
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.

WardNo | WardName
1 | Ward1
2 | Ward2
__________________
1 | Patient1
2 | Patient2


instead of:

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 this post


Link to post
Share on other sites
Sander    1332
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 this post


Link to post
Share on other sites
Jonny_S    149
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 this post


Link to post
Share on other sites
Kylotan    10007
Quote:
Original post by Jonny_S
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.


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 this post


Link to post
Share on other sites
Jonny_S    149
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 info
SELECT WardNo, WardName FROM Ward WHERE WardName = 'Childrens'
REM list patients in childrens ward
SELECT 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 this post


Link to post
Share on other sites
Plasmana    261
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 this post


Link to post
Share on other sites
Jonny_S    149
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...

[edit]

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 WARDNO

SELECT DISTINCT Ward.WardNo, WardName, FirstName FROM Ward INNER JOIN
WardPatient 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 this post


Link to post
Share on other sites
Jonny_S    149
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 this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this