Jump to content
  • Advertisement
Sign in to follow this  
Jonny_S

[web] SQL Question

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

If you intended to correct an error in the post then please contact us.

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


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


Link to post
Share on other sites
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
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
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
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
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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!