I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
i'm somewhat at a loss of where to begin.
Complex query involving multiple tables
paketman
Yes i did put that database together actually. It was my first 'production' database. i've tinkered for years on a personal level but have created my first ASP.NET/C# app with the SQL database on the backend for a program at work to keep track of employee training.
Thanks for the script. It worked like a charm. I had done some research yesterday and had determined that I probably needed a case statement but wasn't sure how to construct. I was trying to make it one complex statement. Breaking it down into the two views and then creating the select hadn't crossed my mind. Thanks again!
darkvader
Cool. Yeah, I was impressed that it was really very normalized. I would loved to have changed your table and column names from tblEmployee and empId to employee and employeeId, respectively, but the structure was way more important.
It gave me something to do while watching the really boring bball game tonight (I wanted Florida to win since I am a SEC fan, but wow. what a snore..)
PB Poonam
If you can put some actual SQL structure including foreign keys (and data) to the talbes it might be easier to see. Your column names can be daunting to try to traverse.
It is an interesting query, but it will take a bit of working out, and some data would make it much easier.
Jerry Long
i'm attaching a script to create a database, the tables, and some sample data.
guitarmanchu
Good script. Because you did that, I wrote the following query. I like the table design. Did you design this
Hopefully you can follow this. I broke it up into two views and a query to hopefully make it clearer... If you don't understand, just ask :)
--first, the employee and what is needed
create view employeeNeeds
as
select e.empId, jt.jobtID, ej.ejID, t.trnId
from tblEmployee as e
join tblJobTitle as jt
on e.jobtID = jt.jobtId
join tblEduJob as ej
on ej.jobtId = jt.jobtid
join tblTraining as t
on t.trnId = ej.trnId
go
--next, get the employee's training history
create view employeeTraining
as
select empId, trnId, max(latest) as currentTraining
from ( select e.empId, tr.trnId, case when tr.trnRevRev = (select max(inn.trnRevRev) as trnRevRev
from tblTrnRev as inn
where inn.trnId = tr.trnId) then 1 else 0 end as latest
from tblEmployee as e
join tblEducation as ed
on e.empId = ed.empId
join tblTrnRev as tr
on ed.trnRevId = tr.trnRevId ) as history
group by empId, trnId
go
select e.empLName, e.empFName, t.trnName, case when currentTraining is null then 'Not Taken'
when currentTraining = 1 then 'Current'
when currentTraining = 0 then 'Taken, Not Current' end
from employeeNeeds as en
join tblEmployee as e
on e.empId = en.empId
join tblTraining as t
on t.trnId = en.trnId
left outer join employeeTraining as et
on en.empId = et.empId
and en.trnId = et.trnId
order by e.empLName, e.empFName, t.trnName