Get Test Not attended by users

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 2008 Forums

Transact-SQL (2008)

Get Test Not attended by users

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2011-06-08 : 06:03:42

Get the TestId and UserID who not attended the test.

DECLARE @Emp TABLE(UserID INT)

INSERT INTO @Emp
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10

SELECT * FROM @Emp

DECLARE @Test TABLE(TestID INT, TestName VARCHAR(50))
INSERT INTO @Test
SELECT 1, 'Test1' UNION ALL
SELECT 2, 'Test2'

SELECT * FROM @Test

DECLARE @TestStatus TABLE(TestID INT, UserID VARCHAR(50))
INSERT INTO @TestStatus
SELECT 1, 1 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 7 UNION ALL
SELECT 1, 8 UNION ALL
SELECT 1, 9 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 8

SELECT * FROM @TestStatus
--This is the final result I need to get
--Users not attended the test
SELECT 1, 2 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 1, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 9 UNION ALL
SELECT 2, 10

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 07:57:17

[code]SELECT
TestID,
UserId
FROM
@Emp e CROSS JOIN @Test t
WHERE NOT EXISTS
(
SELECT * FROM @TestStatus ts
WHERE ts.TestID = t.TestId AND ts.UserID = e.UserId
)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources