POS/410 Week 2 – On-Ground
Scripts – Retrieving Data from Multiple Tables Using Joins
The purpose of this lecture is to provide the student with an introduction to the basics of retrieving information from more than one table using actual scripts. Most scripts follow the instructor supplied material on rEsource. But the scripts below are additional scripts created by the author. The database is a simple four table database used to track project assignments. The queries illustrate the use of various joins.
You should be able to select the appropriate script text and copy it into the SQL Server Query Analyzer and run it. Note these scripts use both syntaxes for joining tables: the older where clause and the newer ANSI style. You should use the latter, but be aware there is much legacy code using the older form.
There are two main types of join: INNER JOIN and OUTER JOIN. These scripts illustrate (or will) both.
Here is link to a diagram of our projects database for reference in using the SQL scripts below.
The following is an inner join using the older syntax
/* for each project retrieve
the project number,
project name and employees assigned*/
SELECT pnumber, pname, essn
FROM project, works_on
WHERE pnumber = pno
ORDER BY pnumber;
The following is the same query using the newer ANSI syntax
/* for each project retrieve
the project number,
project name and employees assigned*/
SELECT pnumber, pname, essn
FROM project
INNER JOIN works_on
ON pnumber = pno
ORDER BY pnumber;
The following query joins three tables.
/* for each project retrieve the project number,
project name and employees assigned*/
SELECT pnumber, pname, essn, lname, fname
FROM project
INNER JOIN works_on
ON pnumber = pno
INNER JOIN employee
ON essn = ssn
ORDER BY pnumber, lname;
The following query uses a left join to find unmatched records.
/* find employees not assigned to a project */
SELECT e.*
FROM employee AS e
LEFT OUTER JOIN works_on
ON ssn = essn
WHERE essn IS NULL;
The following query joins the employee table to itself. It requires the use of table aliases.
/*list all employees and the
names of their managers*/
SELECT e.ssn, e.fname, e.lname, m.fname, m.lname
FROM employee as e, employee as m
WHERE e.superssn = m.ssn
ORDER BY e.ssn;
Dan
D'Urso
Laguna Niguel, CA
April 2005