SELECT Help - 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 2012 Forums

Transact-SQL (2012)

SELECT Help

Author

Topic

beatkeeper25
Starting Member

27 Posts

Posted - 2014-10-22 : 10:25:49

I'm trying to determine which UserIDs have don't have a record for all 7 safety training courses in the course table. Criteria for the safety courses are CourseTypeID=1 and IsActive=1, or I could just list the 7 courseIDs.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 11:49:18

1. this sounds like homework. If so, you need to actually try to write the query, then post it and your results if you still need help
2. if this is not homework, you need to post the table schema, some sample data, and expected results.

beatkeeper25
Starting Member

27 Posts

Posted - 2014-10-22 : 12:36:49

Not homework, just on a time crunch. Here's what I came up with. Seems to be working, still testing.

select userid from CourseRecords
where userid in (select m.userid from member m join assignment a on a.userid=m.userid where orgid=@orgid and IsDefault=1)
AND userid NOT IN (
select m1.userid from CourseRecords m1
join CourseRecords m2 on m1.userid=m2.userid and m1.CourseID=1326 and m2.CourseID=1327
join CourseRecords m3 on m1.userid=m3.userid and m3.CourseID=1328
join CourseRecords m4 on m1.userid=m4.userid and m4.CourseID=1771
join CourseRecords m5 on m1.userid=m5.userid and m5.CourseID=2294
join CourseRecords m6 on m1.userid=m6.userid and m6.CourseID=4002
join CourseRecords m7 on m1.userid=m7.userid and m7.CourseID=17897
where m1.userid IN (select m.userid from member m join assignment a on a.userid=m.userid where orgid=@orgid and IsDefault=1))

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 14:25:45

how about:


select userid from CourseRecords c
join assignment a on c.userid = a.userid 
except
select userid from CourseRecords
where Courseid in(1326, 1237, ..., 17897)
and <your other where conditions)

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-24 : 12:16:40

If this code is for a business, keep your solution as generic as possible. I would use the filters CourseTypeID=1 and IsActive=1 instead of listing the CourseIds individually. If a course is added or removed, your logic would still work in one case whereas the other would need to be modified.

Also, I am confused about a couple pieces. You indicate that you want "UserIDs that don't have a record". I am wondering if it is possible for a user to have an assignment but not a courserecord or vice-versa. Which table has CourseTypeID=1 and IsActive=1? It seems to me that normalization rules are being broken if CourseRecord has both a userID and CourseTypeID, so I want to make sure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources