SELECT help
-
I have three tables, Users (PK = UserID), Activities (PK = ActivityID), and Attempts (FK = UserID and ActivityID). The Attempts table contains data for when a user has registered for a particular activity. What I need to accomplish is to come up with a list of userIDs that are not registered for an activity and the ActivityIDs that they are not registered for. I am drawing a blank on how to accomplish this. A sample of the three tables is below:
Users
UserID UserName
1 Joe
2 Bob
3 Dave
Activities
ActivityID ActivityName
1 Act1
2 Act2
3 Act3
Attempts
UserID ActivityID
2 2
2 3
1 1
1 3
Based upon that data my exception listing would look like this
UserID ActivityID
1 2
2 1
3 1
3 2
3 3
How is the best way to achieve that result?
Thanks,
Matt
-
Select distinct u.userId, a.activityId
from users u, activities a
where not exists (select 1 from attempts t
where t.userid = u.userid and t.activityid = a.activityid)
_____________
Code for TallyGenerator -
G'day,
This is one of the rare cases where a cross join makes sense. Be careful if you use this solution in a real production situation. Performance may demand a slightly different solution. Having said that, here is an easy example.
Hope this helps
Wayne
EDIT: Darn! Sergiy beat me to it!
CREATE TABLE Users (
UserID INT,
UserName VARCHAR(50)
)
GO
INSERT INTO Users (UserID, Username)
SELECT 1, 'Joe' UNION ALL
SELECT 2, 'Bob' UNION ALL
SELECT 3, 'Dave'
GO
SELECT * FROM Users
GO
CREATE TABLE Activities (
ActivityID INT,
ActivityName VARCHAR(50)
)
GO
INSERT INTO Activities (ActivityID, ActivityName)
SELECT 1, 'Act1' UNION ALL
SELECT 2, 'Act2' UNION ALL
SELECT 3, 'Act3'
GO
SELECT * FROM Activities
GO
CREATE TABLE Attempts (
UserID INT,
ActivityID INT
)
GO
INSERT INTO Attempts (UserID, ActivityID)
SELECT 2, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 1, 3
GO
SELECT * FROM Attempts
GO
SELECT U.UserID, Act.ActivityID
FROM Users U
CROSS JOIN Activities Act
LEFT OUTER JOIN Attempts Att ON Att.UserID = U.UserID AND Att.ActivityID = Act.ActivityID
WHERE Att.UserID IS NULL
-
Thanks guys. Those both work great.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply