Select Statement question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Statement question

Author

Topic

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 10:23:47

I am wondering if someone can help me...

I have 2 tables
-CompletedTests
-Required Tests

In the Required Tests for each person, there will be any number of test ids that the person will need to take for the year (can range from 1 to 5 test ids)

In the Completed Tests, this will show what tests that they have taken for the year.

I'm trying to write a select statement that will return true if the person has Completed all of the tests (shown in the CompletedTests) that are shown in the Required Tests table. So basically only if they match record for record.

I know how to show those if they meet one of the tests but not sure how to do it if they meet ALL of the required tests.

Any suggestions appreciated

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-18 : 10:35:46

do you have maybe a third table : Persons?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 10:37:24

can you please post the table structures of above tables and some sample data and expected o/p

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 10:37:50

Yes I have many other tables and one of the other tables (HRInfo) has all the information on each person such as employee ID, name, Dept etc. I would use Employee ID in the query. Sorry I forgot to put that info my question

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 10:47:22

ReqCourse:
EmpID
TestID

CompletedCourse:
RecordID
EmpID
TestID
TestYear
Score
DateCompleted

HRInfo:
EmpID
FirstName
LastName
Department
JobTitle

Example:
Joe has to take testID's 1, 2 and 3 for the year
Joe has only completed testID 1 and 2.

I don't want him to show in the results because he didn't complete test 3 yet. Once he completes test 3 then he should show in the results as completed.

If reqCourse.TestID = CompletedCourse.TestID then show empID, FirstName, LastName, TestID, DateCompleted, Score (all of them) but they have to have completed all of the courses in the reqCourse table to show in the results. That is where I am stuck.

I hope this makes more sense. I have no code to show because I'm not sure on how to write it so I am writing what I'd like for it to do.

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 11:01:36

try this

select empID, FirstName, LastName, TestID, DateCompleted, Score
from reqcourse r
inner join CompletedCourse c on c.empid = r.empid and c.testid = r.testid
inner join hrinfo h on h.empid = r.empid

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 11:10:08

Thanks for the response. That doesn't work. I actually had tried something similar.

It returns the 2 records that were completed (Tests 1 and 2). What I want for it to do is not return anything since he didn't take all 3 tests that were in the required course table.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 11:45:41

try this

declare @reqcourse table ( empid int, testid int )
insert into @reqcourse
select 700, 1 union all
select 700, 2 union all
select 700, 3 union all
select 700, 4 union all
select 800, 1 union all
select 800, 2

declare @CompletedCourse table ( RecordID int identity(1,1),empid int, testid int , testyear int, score int, datecompleted datetime )
insert into @CompletedCourse
select 700,1, 2009, 100,getdate()-10 union all
select 700,2, 2009, 100,getdate()-4 union all
select 700,3, 2009, 100,getdate()-3 union all
select 700,4, 2009, 100,getdate()-2 union all
select 800,1, 2009, 100,getdate()-1

declare @HRInfo table ( empid int, FirstName varchar(50) , LastName varchar(50) , Department varchar(50) , JobTitle varchar(50) )
insert into @HRInfo
select 700,'joe','sui', 'coding' ,'seniorprogrammer' union all
select 800,'Mary','jews', 'database' ,'seniordatabaseprogrammer'

select r.empID, h.FirstName, h.LastName, c.TestID, c.DateCompleted, c.Score
from @reqcourse r
inner join @CompletedCourse c on c.empid = r.empid and c.testid = r.testid
inner join @hrinfo h on h.empid = r.empid
inner join ( select empid,count(testid) as reqcnt
from @reqcourse
group by empid ) req on req.empid = r.empid

inner join ( select empid,count(testid) as compcnt
from @CompletedCourse
group by empid ) comp on comp.empid = c.empid
where comp.compcnt = req.reqcnt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 11:51:07

[code]
SELECT empID, FirstName, LastName, TestID, DateCompleted, Score
FROM
(
SELECT rc.EmpID,
hi.FirstName, hi.LastName, rc.TestID, cc.DateCompleted, cc.Score,
COUNT(rc.TestID) OVER (PARTITION BY rc.EmpID) AS TotalTests,
COUNT(cc.TestID) OVER (PARTITION BY rc.EmpID) AS AttemptedTests
FROM ReqCourse rc
INNER JOIN HRInfo hi
ON hi.EmpID=rc.EmpID
LEFT JOIN CompletedCourse cc
ON cc.EmpID=rc.EmpID
AND cc.TestID=rc.TestID
)t
WHERE TotalTests=AttemptedTests
[/code]

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 12:13:12

Thank you both for your help! I actually tried the Select statement from Raky and it worked. Exactly what I needed it to do!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:15:47

quote:
Originally posted by sqlNeebie

Thank you both for your help! I actually tried the Select statement from Raky and it worked. Exactly what I needed it to do!!

try mine too and compare execution times.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 12:22:18

Welcome....Eventhough both solutions work for you Visakh solution is better than my solution...

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 12:48:32

Thanks again.

I have never used Partion By and Over so I am not sure what it is doing. Also, I just realized that while the solutions do work - it still isn't completely what I need.

You see the employees can take other courses that are not 'required' so in the event someone takes one correct course and one incorrect course (for a total of 2) and the required coures = 2 then it would technically be ok since the counts match but really it isn't because the courses don't match test IDs. I will keep playing around with this

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:53:39

quote:
Originally posted by sqlNeebie

Thanks again.

I have never used Partion By and Over so I am not sure what it is doing. Also, I just realized that while the solutions do work - it still isn't completely what I need.

You see the employees can take other courses that are not 'required' so in the event someone takes one correct course and one incorrect course (for a total of 2) and the required coures = 2 then it would technically be ok since the counts match but really it isn't because the courses don't match test IDs. I will keep playing around with this

Thanks again

Nope. since i'm joining to CompletedCourse from ReqCourse on EmpID and TestID, it will return matches only for required tests. so count(cc.TestID) will only return count of those tests which are in ReqCourse table (required test). then i'm comparing this with total tests in required for that employee and if they match he's selected. isnt this what you want?
if this is still not what you want, can you please explain the problem with some data samples?

sqlNeebie
Starting Member

7 Posts

Posted - 2009-02-18 : 13:41:12

No you are right, Visakh. It did work (Raky's). I tried yours and I got an error. I did some reading and I think because I'm on sql server 2003, Partion By and Over can't be used ?? Anyway, I had additional code to put in and that is where it didn't look like it was going to work but it did.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 13:43:56

quote:
Originally posted by sqlNeebie

No you are right, Visakh. It did work (Raky's). I tried yours and I got an error. I did some reading and I think because I'm on sql server 2003, Partion By and Over can't be used ?? Anyway, I had additional code to put in and that is where it didn't look like it was going to work but it did.

Thanks again!

sql 2003? there's no such version. probably you meant sql 2000. anyways run below and post back what it returns


SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your database name'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources