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:25

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-29 : 12:40:33

Dupe:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124794

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources