Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Complex query involving multiple tables

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.

[1479 byte] By [mliddekee] at [2007-12-19]
«« Compiling code written in C in .NET2005
»» how to represent xml document on treeviewer using c#
# 1

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.

LouisDavidson-SQLServerMVP at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

i'm attaching a script to create a database, the tables, and some sample data.

create database "HRMS"
ON PRIMARY
( NAME = HRMS, FILENAME = 'C:\DB\HRMS.MDF', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
LOG ON
( NAME = HRMS_log, FILENAME = 'C:\DB\HRMS_log.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )

go

use "HRMS"

go

create table "tblEduJob" (
"ejID" uniqueidentifier not null,
"jobtID" uniqueidentifier null,
"trnID" uniqueidentifier null)

go

alter table "tblEduJob"
add constraint "tblEduJob_PK" primary key ("ejID")


go

create table "tblTrnRev" (
"trnrevID" uniqueidentifier not null,
"trnID" uniqueidentifier not null,
"trnrevRev" int not null,
"trnrevDate" datetime null)

go

alter table "tblTrnRev"
add constraint "tblTrnRev_PK" primary key ("trnrevID")


go

create table "tblEducation" (
"eduID" uniqueidentifier not null,
"empID" uniqueidentifier not null,
"trnrevID" uniqueidentifier not null,
"eduDate" smalldatetime null)

go

alter table "tblEducation"
add constraint "tblEducation_PK" primary key ("eduID")


go

create table "tblJobTitle" (
"jobtID" uniqueidentifier not null,
"jobtName" varchar(25) not null)

go

alter table "tblJobTitle"
add constraint "tblJobTitle_PK" primary key ("jobtID")


go

create table "tblTraining" (
"trnID" uniqueidentifier not null,
"trnNbr" varchar(8) null,
"trnName" varchar(50) not null,
"trnDesc" text null,
"trnSOP" bit null)

go

alter table "tblTraining"
add constraint "tblTraining_PK" primary key ("trnID")


go

create table "tblDept" (
"deptID" uniqueidentifier not null,
"deptNbr" varchar(4) null,
"deptName" varchar(25) null)

go

alter table "tblDept"
add constraint "tblDept_PK" primary key ("deptID")


go

create table "tblEmployee" (
"empID" uniqueidentifier not null,
"empNbr" varchar(10) not null,
"empLName" varchar(30) null,
"empFName" varchar(20) null,
"deptID" uniqueidentifier null,
"jobtID" uniqueidentifier null)

go

alter table "tblEmployee"
add constraint "tblEmployee_PK" primary key ("empID")


go

alter table "tblEduJob"
add constraint "tblTraining_tblEduJob_FK1" foreign key (
"trnID")
references "tblTraining" (
"trnID")

go

alter table "tblEduJob"
add constraint "tblJobTitle_tblEduJob_FK1" foreign key (
"jobtID")
references "tblJobTitle" (
"jobtID")

go

alter table "tblTrnRev"
add constraint "tblTraining_tblTrnRev_FK1" foreign key (
"trnID")
references "tblTraining" (
"trnID")

go

alter table "tblEducation"
add constraint "tblEmployee_tblEducation_FK1" foreign key (
"empID")
references "tblEmployee" (
"empID")

go

alter table "tblEducation"
add constraint "tblTrnRev_tblEducation_FK1" foreign key (
"trnrevID")
references "tblTrnRev" (
"trnrevID")

go

alter table "tblEmployee"
add constraint "tblDept_tblEmployee_FK1" foreign key (
"deptID")
references "tblDept" (
"deptID")

go

alter table "tblEmployee"
add constraint "tblJobTitle_tblEmployee_FK1" foreign key (
"jobtID")
references "tblJobTitle" (
"jobtID")

go

INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('F8ED13F7-8CC4-4F4A-9D05-0D7A931D375F','3000','PRODUCTION')
INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('EBC02D85-3518-4982-827C-359645343A5F','1000','ACCOUNTING')
INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('5AB270BD-3127-43CD-839E-56B3D5A30B85','4000','ADMIN')
INSERT INTO [tblDept] ([deptID],[deptNbr],[deptName])VALUES('7F787D86-1C3A-4118-9521-9853D49C4708','2000','SALES')

INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('EF49546A-7A5E-43BE-B3B1-04B72A498301','ACCOUNTANT')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('720ECD1A-7A2B-498F-A43B-073C689D4CC4','SALESMAN')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('4CAF209A-4151-49AE-B103-5D677B4D829C','CEO')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('490EC42E-3E33-410D-B489-9A4CE6F7AAE0','CFO')
INSERT INTO [tblJobTitle] ([jobtID],[jobtName])VALUES('5A7DD66C-F5C9-44E1-8716-A666C215359E','MACHINE TENDER')

INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('DFDF932E-5C09-4B72-A1D4-1A39BC821ACF','1001','SMITH','JOHN',1,'EBC02D85-3518-4982-827C-359645343A5F','490EC42E-3E33-410D-B489-9A4CE6F7AAE0')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('9F71DE9E-A6C1-4FA7-A822-620893717403','1005','CROCKETT','DAVIE',NULL,'F8ED13F7-8CC4-4F4A-9D05-0D7A931D375F','5A7DD66C-F5C9-44E1-8716-A666C215359E')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('E0DE6887-08AD-4911-95FC-A4A3D91358DF','1004','DOE','JACK',NULL,'7F787D86-1C3A-4118-9521-9853D49C4708','720ECD1A-7A2B-498F-A43B-073C689D4CC4')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('99DEF2F2-4CE7-4862-BB6C-B3EC3390532F','1002','JONES','BOB',NULL,'EBC02D85-3518-4982-827C-359645343A5F','EF49546A-7A5E-43BE-B3B1-04B72A498301')
INSERT INTO [tblEmployee] ([empID],[empNbr],[empLName],[empFName],[empActive],[deptID],[jobtID])VALUES('D7C4CE72-700E-4F21-A90B-C5E413EAAABE','1003','SMITH','JANE',NULL,'5AB270BD-3127-43CD-839E-56B3D5A30B85','4CAF209A-4151-49AE-B103-5D677B4D829C')

INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('0FBB1202-1A96-4FD0-88F5-5501EC848307','2','CLASS 2',0)
INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('C89FE8A1-B993-4CE0-B092-6D6982AD30B3','1','CLASS 1',0)
INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4','4','CLASS 4',0)
INSERT INTO [tblTraining] ([trnID],[trnNbr],[trnName],[trnSOP])VALUES('FD3769D6-EF93-4170-BF23-E29CDEE5934D','3','CLASS 3',0)

INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('40B4ADAF-C4E8-48D8-835D-1337E5325BA8','C89FE8A1-B993-4CE0-B092-6D6982AD30B3',0,'Jan 1 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('CFE261A1-8A62-4523-BF10-1A4B70DC0A89','FD3769D6-EF93-4170-BF23-E29CDEE5934D',0,'Jan 1 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('BD3B2F29-F76D-425D-A15A-2AB442F607EB','0FBB1202-1A96-4FD0-88F5-5501EC848307',0,'Apr 3 2006 4:20:21:000PM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','C89FE8A1-B993-4CE0-B092-6D6982AD30B3',2,'Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('22546AB5-F39A-409F-AB99-632AD129618D','C89FE8A1-B993-4CE0-B092-6D6982AD30B3',1,'Jan 2 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('50FB73E1-7B96-4FD6-BFF9-DC282576176F','FD3769D6-EF93-4170-BF23-E29CDEE5934D',1,'Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblTrnRev] ([trnrevID],[trnID],[trnrevRev],[trnrevDate])VALUES('F537B949-35CB-491F-94D9-E1B8CD392921','3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4',0,'Apr 3 2006 4:20:34:000PM')

INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('31BF04D5-1502-4DE8-9804-066BD20F3A84','490EC42E-3E33-410D-B489-9A4CE6F7AAE0','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('50907488-2272-4084-9FAC-07452AD8A21F','720ECD1A-7A2B-498F-A43B-073C689D4CC4','3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('9579F27B-FD0D-4CBF-8C8D-1D4327FBC86B','EF49546A-7A5E-43BE-B3B1-04B72A498301','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('5DB85977-4E34-4106-BD92-292F69480569','5A7DD66C-F5C9-44E1-8716-A666C215359E','FD3769D6-EF93-4170-BF23-E29CDEE5934D')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('863A24F8-4085-4B0B-A840-571D32038E9A','720ECD1A-7A2B-498F-A43B-073C689D4CC4','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('2DE5211B-12FE-40A5-8286-660B0364DDD1','EF49546A-7A5E-43BE-B3B1-04B72A498301','FD3769D6-EF93-4170-BF23-E29CDEE5934D')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('04E83A51-CD45-48C9-9ACD-690B1A24CB93','5A7DD66C-F5C9-44E1-8716-A666C215359E','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('ED3C34DA-E48E-49EE-8A2D-70009720CE31','490EC42E-3E33-410D-B489-9A4CE6F7AAE0','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('7F71E446-05CB-47F8-9071-7CDBF404FCBB','720ECD1A-7A2B-498F-A43B-073C689D4CC4','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('F09BBA4D-F834-41C5-ACF3-A5862226A9C9','EF49546A-7A5E-43BE-B3B1-04B72A498301','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('027A62E5-A9D9-4666-9AB4-BC2CDC995807','4CAF209A-4151-49AE-B103-5D677B4D829C','0FBB1202-1A96-4FD0-88F5-5501EC848307')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('BC8DD185-DAAA-4E90-A483-D48D73E75291','4CAF209A-4151-49AE-B103-5D677B4D829C','C89FE8A1-B993-4CE0-B092-6D6982AD30B3')
INSERT INTO [tblEduJob] ([ejID],[jobtID],[trnID])VALUES('F392F54B-0F71-4FAC-A5B8-F822B0ED7597','EF49546A-7A5E-43BE-B3B1-04B72A498301','3FE8DA67-C4DB-4F01-8595-DCFAFBC239C4')

INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('DD2B3BF8-5548-4F6F-A664-0372333A1C59','D7C4CE72-700E-4F21-A90B-C5E413EAAABE','54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('65AB167B-2785-4966-8266-299B57B92BAA','D7C4CE72-700E-4F21-A90B-C5E413EAAABE','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('86E5FFDE-A732-4B7E-9254-57ABAD89BE7F','DFDF932E-5C09-4B72-A1D4-1A39BC821ACF','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('7572DF39-0274-461C-9824-5C524DDD2DEF','E0DE6887-08AD-4911-95FC-A4A3D91358DF','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('C195DC05-B577-4A13-A51E-86694B8C8C36','99DEF2F2-4CE7-4862-BB6C-B3EC3390532F','54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','Apr 3 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('3B3D46CF-76C1-42DF-9AA0-D026CF7D9CB1','9F71DE9E-A6C1-4FA7-A822-620893717403','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('8805BC51-850D-4A38-85D3-D7AF7ECC90B5','99DEF2F2-4CE7-4862-BB6C-B3EC3390532F','40B4ADAF-C4E8-48D8-835D-1337E5325BA8','Feb 1 2006 12:00:00:000AM')
INSERT INTO [tblEducation] ([eduID],[empID],[trnrevID],[eduDate])VALUES('49C849AF-1362-4889-87F7-EA83F26F327E','E0DE6887-08AD-4911-95FC-A4A3D91358DF','54DD0DAC-4E2C-45A2-9EE9-37A524ED9084','Apr 3 2006 12:00:00:000AM')

mliddekee at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

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

LouisDavidson-SQLServerMVP at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 4

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!

mliddekee at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
# 5

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..)

LouisDavidson-SQLServerMVP at 2007-9-9 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified