How to gather information from multiple tables

Question

I am new to SQL scripting and would be grateful for any assistance on the following;

I need to gather information from various different tables. I found the information required by running the following scripts.

SELECT [immunisation_id]
,[patient_id]
,[course_id]
,[date_due]
,[date_given]
,[result]
,[comments]
FROM [Cohort].[dbo].[immunisations]

SELECT [course_id]
,[description]
FROM [Cohort].[dbo].[courses]

SELECT [patient_id]
,[title_id]
,[first_name]
,[last_name]
,[dob]
FROM [Cohort].[dbo].[patients]

SELECT [title_id]
,[description]
FROM [Cohort].[dbo].[titles]

SELECT [employee_id]
,[patient_id]
,[post_title_id]
,[department_id]
,[directorate_id]
FROM [Cohort].[dbo].[employees]

SELECT [post_title_id]
,[description]
 FROM [Cohort].[dbo].[post_titles]

SELECT [department_id]
,[description]
FROM [Cohort].[dbo].[departments]

I now need to return results on patients who have had immunisations only and include information from the following tables & columns;

description from dbo.titles
first_name, last_name, dob from dbo.patients
description from dbo.post_titles
description from dbo.departments
description from dbo.courses
date_due, date given, result, comments from dbo.immunisations

I have read that inner joins will do this but don't know how to compile the script.

Answer 1

Many thanks for your input. It was very much appreciated. After reading them all and watching a few tutorials I managed to figure it out; This is what worked. I also wanted to point out that [] is microsoft specific for those few that didn't know. However I did think it was a good idea to remove them since the script can be used for say an Oracle environment.

select *

from patients

join immunisations on patients.patient_id = immunisations.patient_id

join titles on patients.title_id = titles.title_id

join courses on immunisations.course_id = courses.course_id

join departments on patients.patient_id = departments.department_id

join employees on patients.patient_id = employees.post_title_id

Best regards

Louise

Answer 2

You can do this to join from multiple tables -

SELECT a.description, b.description, c.description, d.first_name --(so on.. and other columns you need)
from TABLEA as a
INNER JOIN TABLEB as b
     on a.commonattribute = b.commonattribute
INNER JOIN TABLEC as c
    on a.commonattribute = c.commonattribute or b.commonattribute = c.commonatribute
--so on to all the required tables...

WHERE (some condition)
ORDER BY (some column);

So in your case, (took a while to combine all tables)

SELECT 

p.patient_id
,p.title_id
,p.first_name
,p.last_name
,p.dob

,i.immunisation_id
,i.course_id
,i.date_due
,i.date_given
,i.result
,i.comments

,c.description

,t.description

,e.employee_id
,e.post_title_id
,e.department_id
,e.directorate_id

,p_t.description

,d.description

FROM Cohort.dbo.patients p

inner join Cohort.dbo.immunisations i
ON i.patient_id = p.patient_id

inner join  Cohort.dbo.courses c
ON c.course_id = i.course_id

inner join  Cohort.dbo.titles t
ON t.title_id = p.title_id

inner join  Cohort.dbo.employees e
ON t.patient_id = p.patient_id

inner join  Cohort.dbo.post_titles p_t
ON p_t.post_title_id = e.post_title_id

inner join  Cohort.dbo.departments d
ON d.department_id = e.department_id

This should Work :D

Answer 3

First off, select from the table you want to define the rest. In this case, immunusations

select date_due, date_given, result, comments
from dbo.immunisations i1 -- give it an alias
-- the joins will go in here
where date_given is not null -- this excludes the records where the immunisation has not yet happened

Ok, now let's start joining:

select i1.date_due, i1.date_given, i1.result, i1.comments,
       p2.first_name, p2.last_name, p2.dob
from dbo.immunisations i1
inner join dbo.patients p2
  on p2.patient_id = i1.patient_id -- aliases make it easier to join
where date_given is not null

Unfortunately, you lack a way of joining the employee info to the immunisation (from your provided code, anyway), so you will need to take this logic, find the missing links and apply it.