Help with Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Joins

Author

Topic

Sapphire
Starting Member

4 Posts

Posted - 2007-11-10 : 21:07:09

I'm quite new to Microsoft sql and so please forgive me if I appear a novice. However, could someone please give me help with the following 2 queries:

1. I need to retrieve the names of each ward that currently has at least 5 resident patients.

2. I need to retrieve the names and ward numbers of all patients that have been referred to the Dermatology department (this data is in the doctor specialism column).

The db has the following 4 tables:

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

nurse
(
staff_no CHAR(3),
nurse_name CHAR(12),
ward_no CHAR(2),
supervisor_no CHAR(3),
PRIMARY KEY (staff_no),
FOREIGN KEY (ward_no) REFERENCES ward,
FOREIGN KEY (supervisor_no) REFERENCES nurse
)

patient
(
patient_id CHAR(3),
patient_name CHAR(12),
consultant_no CHAR(3) NOT NULL,
ward_no CHAR(2) NOT NULL,
PRIMARY KEY (patient_id),
FOREIGN KEY (consultant_no) REFERENCES doctor,
FOREIGN KEY (ward_no) REFERENCES ward
)

ward
(
ward_no CHAR(2),
ward_name CHAR(10),
number_of_beds SMALLINT,
PRIMARY KEY (ward_no)
)

Any help would be appreciated

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-10 : 21:23:18

--To get the WARD NAMES (only)

would be:


Select [ward_name] 
From ward

To link to the Patient info (for example):


Select ward_name, patient_name
FROM ward INNER JOIN patient on ward.ward_No = patient.ward_no

To get the ward_name where there are 5 or more patients (your Quest #1)


Select ward_name,CT as patientCount
FROM ward INNER JOIN (Select Count(*) as CT,ward_no 
                      FROM patient
                      Group by ward_no) p on ward.ward_No = p.ward_no
Where CT >=5
Group by ward_name
Order by CT desc

See if that helps you piece together some ideas for #2. I didn't test this stuff, so if there is a syntax error or something (oops, sorry)

HTH

Sapphire
Starting Member

4 Posts

Posted - 2007-11-10 : 21:36:26

Hi dataguru1971,

Thanks for the help.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-12 : 05:08:44

for first query try this
select wardname from ward where wardno in (
select a.wardno from ( select wardno,count(wardno) c from testing group by wardno ) a where a.c>=5)

for second query
plz make it clear where you are storing the information like department name as dermatologist or etc

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 07:14:09

note that there's a difference between join, in and exists:
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

ericajones20
Starting Member

4 Posts

Posted - 2007-11-29 : 15:37:44

Look in the coursework notes I handed out for this, the T-SQL example is near enough what I am asking for

Hey at least now the coursework I handed out is infamous as it madeit onto a forum :)

Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources