Comparing Columns in a Select List Question

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)

Comparing Columns in a Select List Question

Author

Topic

Brian C
Starting Member

10 Posts

Posted - 2009-04-29 : 11:59:24

Hi Everyone,

I was hoping someone can help me with this SQL logic problem. I have searched for an answer on the net for this but cannot find anything.

I am trying to do a select where I must compare 2 columns and depending on what is in each column I include that record or not. For example here is a select resultset for what I am returning.

PersonID...TrainingReq...TrainingPassed
...1............N.............NULL
...1............Y.............T
...1............Y.............T
...1............Y.............T

So in the above example I want to only keep track of Person records that have a TrainingReq flag of Y and a corresponding TrainingPassed of T.
What I need to be able to do is for each PersonID, count the TrainingReq where = Y and count the TrainingPassed where = T. I then need to compare the two results and if they equal then include this record, I do this in the having clause of my select. I only want to include Perrsons that passed all their training. There are 4 records in the example and that shows 1 record for each period, which may be up to 4.

The problem I am running up against is how do I do the select. I can do a select and count but when I count it simply counts the Y's and N's.

select p.PersonID,
count(t.trainingreq),
count(t.trainingpassed)
from Person p
inner join training t on p.trainingid = t.trainingid
group by p.personid
having (count(t.trainingreq)=count(t.trainingpassed)

I know this is wrong but can someone please point me in the right direction? I think I need to do an embedded select in either the select or the having clause.

Thanks for any help

Brian

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-29 : 12:08:49

SELECT a.PersonId, b.totalTrainingReq , b.totalTrainingPassed FROM Person a INNER JOIN(SELECT personid, COUNT(trainingreq) AS totalTrainingReq, COUNT(trainingpassed) AS totalTrainingPassed FROM training GROUP BY personid) AS b ON a.PersonID = b.PersonId WHERE b.totalTrainingReq = b.totalTrainingPassed

Brian C
Starting Member

10 Posts

Posted - 2009-04-30 : 11:48:23

Hi and thanks for the example. I was able to solve the problem but had to do 2 inner joins to the training table, one for each count as I had to only return records that met the following:
TrainingReq where = Y and count the TrainingPassed where = T
Once I did that it worked great.

Thanks so much for the help you've been a huge help.

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources