Jump to content
Sign in to follow this  
tyno

Trouble with SQL exercise

Recommended Posts

I'm trying to complete some SQL exercises for my distance course, I got stuck on a couple questions and it would take a couple days before I get help from the lecturer/teacher but I think I nearly have it finished. Any feedback would be appreciated.

 

I have 3 tables, with the following columns:

 

Admission

Admission_id, Patient_id

 

Person

Person_id, Surname, First_name, Next_of_kin

 

Staff

Person_id

 

 

What I am trying to do if show the patients that have been admitted, that have next of kin that are staff members, without using sub queries. At the moment I'm trying to filter based on the records of a single column, but thats probably way off what I need to do.

Going by the study guide I could solve this by joining all the tables together using 2 inner joins, I attempted to do that but access disliked the contents of my select when I did that. So this is what I have instead;

 

SELECT p.Person_id, p.Surname, p.First_name, p.Next_of_kin

FROM person p

INNER JOIN staff s ON p.Person_id = s.Person_id

WHERE p.next_of_kin IS NOT NULL

OR (p.next_of_kin = p.person_id(*))

GROUP BY p.Person_id, p.Surname, p.First_name, p.Next_of_kin;

 

I'm using Microsoft Access 2007, if that changes anything.

Share this post


Link to post
Share on other sites

I think you need to work on your database normalisation more.

 

What does the Staff table store? Is it just a single column of Person_id, representing Persons that are Staff? If so, you should store that in a Yes/No field on the Person and save yourself a join.

What does Person store? You talk about Pateints but where are they stored? In Person table? You need a discriminator column to identify Patients. In that light, Use a PersonType column that tells if the person is a Patient or a Staff member (and scrap the Yes/No field for Staff I mentioned earlier).

What does the Adminision table hold? Normally a table with two key columns is used to map M:N relationships (many-to-many) where both colmns are Foriegn Keys to their respective tables.

 

A better schema would look like

[b]Admissions[/b] [[u]Admission_Id, Patient_Id[/u]]
[b]Person[/b] [[u]Person_Id[/u], Surname, First_Name, Next_Of_Kin, PersonType]

Ignoring that and going on your schema...

I'm going to make some assumptions that Next_of_kin is a foriegn_key back to the Person table, referencing Person_id.

 

SELECT p.Person_Id, p.Surname, P.First_Name
FROM Person p
INNER JOIN Staff s on s.person_id=p.next_of_kin
That will get what you want, assuming next_of_kin is an FK to person_id. Where's my second inner join? don't need it... I guess if you really want 2 inner joins....

SELECT p.Person_Id, p.Surname, P.First_Name
FROM Person p
INNER JOIN person ps on ps.person_id=p.next_of_kin
INNER JOIN staff s on ps.person_id=s.person_id
Is redundant though.

 

I'd design a different schema...

Share this post


Link to post
Share on other sites

There is a lot more to the tables than what I posted, but those where the relevant columns. I need to have the patients with staff as next of kin AND the patient themselves, thats where I get stuck. I always end up with one or the other.

 

Thanks for the swift reply kikz.

 

Edit: Btw the tables are the ones we have been given to work with, or the solution would be much easier I think.

Edited by tyno

Share this post


Link to post
Share on other sites

There is a lot more to the tables than what I posted, but those where the relevant columns. I need to have the patients with staff as next of kin AND the patient themselves, thats where I get stuck. I always end up with one or the other.

 

Thanks for the swift reply kikz.

 

Edit: Btw the tables are the ones we have been given to work with, or the solution would be much easier I think.

If you need the names of the next of kin you just project the columns from the appropriate table

SELECT p.Person_Id, p.Surname, P.First_Name, ps.Surname 'Next of kin Surname', ps.first_name 'Next of kin first name'
FROM Person p
INNER JOIN person ps on ps.person_id=p.next_of_kin
INNER JOIN staff s on ps.person_id=s.person_id

Share this post


Link to post
Share on other sites

I figured it out, probably not the best way to do it though, that returns the patient and the next of kin that is a staff member, albeit not in the prettiest way. Thanks for the ideas.

 

 

I have another pickle I ran into a bit further down in the exercises. It essentially requires me to remove any rows that have a primary key that is a foreign key in another table, currently I am not sure how I can do that without everything getting messy using joins again, I'll see what I come up with.

 

Edit: Wasn't too hard, used EXISTS.

Edited by tyno

Share this post


Link to post
Share on other sites

I figured it out, probably not the best way to do it though, that returns the patient and the next of kin that is a staff member, albeit not in the prettiest way. Thanks for the ideas.

Sharing is caring :P

 

Rob.

Share this post


Link to post
Share on other sites

EXISTS uses a subquery...

 

The actual schema would have been helpful too; actually knowing the type of next_of_kin. My response is correct given the assumptions i outlined :)

Share this post


Link to post
Share on other sites

I'd like to get it checked by the lecturer then see if he can help me get it better before I post my monstrosity.

 

 

Here it is:

 

SELECT DISTINCT person.First_name, person.Surname, Nextofkin.First_name, Nextofkin.Surname
Added 2 new rows and duplicated the person table to finish this.

FROM person AS Nextofkin INNER JOIN ((person INNER JOIN admission ON person.Person_id = admission.Patient_id)
There has to be another way to do this.....

INNER JOIN staff ON person.Next_of_kin = staff.Person_id) ON Nextofkin.Person_id = person.Next_of_kin;

 

Getting a little wall-o'-text like, tried to break it up.

 

Edit: Added it in its original form.

Edited by tyno

Share this post


Link to post
Share on other sites

I assume the Person table contains details of staff, patients or any person of interest in the same table?

Share this post


Link to post
Share on other sites

I assume the Person table contains details of staff, patients or any person of interest in the same table?

I don't think so, at least not from what I was led to believe, given there is a Staff table. My personal preference is for table-per-class-hierarchy so I initially assumed the same as you. Daftly the table seems to be storing Patients In a Person table, which gives the impression of table-per-class-hierarchy and NextOfKin is just a regular person (at least as far as domain models go).

 

Probably yet another TAFE/University assignment that doesn't reflect how things are done in the real world :D

 

 

tyno, FYI, table-per-class-hierarchy basically -- when you model your objects, you'd identify Person, Patient, Next of Kin, Staff as related entities (things in a system). All share common attributes such as Name, Date Of Birth, Address, and so on; attributes that are common to all people. Each subclass has it's own specific properties. Patients might have treatments, bed (which would have associated room and ward). Staff would have a payroll Id, department etc. Next of Kin might just be a plain Person, or it could be a patient or a Staff member.

 

When you model these things in a db, you can either store the all the properties related to every Person in it's on table and create extra tables for each type of person (subclass) eg a table for each of Patient, Staff, etc. Or you can add the fields from each of Patient and Staff to the Person table (with NULL enabled). In that case you use a discriminator to identify what type of person each tuple in the database represents. EG PersonType = 'Person', 'Patient', 'Staff' and so on (might get more tricky if your staff member becomes a patient :p).

Share this post


Link to post
Share on other sites

In real world, the staff tables and patient tables would be separate. Uni assignments never reflect the real world, as the person teaching often never worked in the real world.

Share this post


Link to post
Share on other sites

In real world, the staff tables and patient tables would be separate. Uni assignments never reflect the real world, as the person teaching often never worked in the real world.

They are separate in the provided assignment example...

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  

×