What kind of join, if any, is appropriate here?

Question

I'm new to (my)SQL and I need to know how (or if it's even possible) to build a particular query.

I have 3 tables, simplified below:

Actions
-------
actionID | staffID | actionDetails | actionDate


Staff
-----
staffID | departmentID | userName | firstName | lastName


Departments
-----------
departmentID | departmentName

As you can see, departmentID is a FK in the Staff table, and staffID is a FK in the Actions table.

I want to pull the following fields based on departmentID:

• userName
• firstName
• lastName
• actionDetails
• actionDate

Can this be done in a single query with joins or do I need to pull staffIDs and then iterate through them?

Again, I'm new at this and it's kinda wrecking my brain at the moment. I don't think I'm cut out for this!

Answer 1

Try this Query.

select 
S.userName,
S.firstName,
S.lastName,
A.actionDetails,
A.actionDate
from Departments D 
join Staff S on d.departmentID=s.departmentID
join Actions A on s.staffID=A.staffID
WHERE D.departmentID = YourDeparmentID;

Types Of JOIN in SQl

Answer 2

Absolutely, this can be done with joins. In fact, that's what joins are designed for. When you're writing SQL, you don't want to think in terms of iterating through IDs; instead, think of getting the data all at once, in "sets." You can use your foreign keys to expand those sets, essentially creating one large set that encompasses multiple tables.

SELECT Staff.userName, Staff.firstName, Staff.lastName, 
  Actions.actionDetails, Actions.actionDate
FROM Staff
INNER JOIN Departments ON Departments.DepartmentID = Staff.DepartmentID
INNER JOIN Actions ON Actions.StaffID = Staff.StaffID

You'll notice that in other answers, folks have used aliases such as Staff as s to simplify their statements. This is pretty common, and makes things more readable; I've left those out here to make the join syntax a little clearer.

The example I've provided uses inner joins -- it will return only records where a match can be made between Staff, Departments, and Actions. If you want to return all rows for one or more tables, and return null for the other tables when a match can't be found, you can use outer joins.