October 21, 2015 at 10:21 am
Hi all,
This is my first post so bare with me, I'm a newbie.
What I'm trying to do is this:
I have a set of users who all have a number of ID's assigned to them. I also have a set of all possible ID's a user can have. What I want to do is select all of the ID's that a user does not have, for each user in the original set. The caveat is that I am trying to do this without a loop (cursor). I know a tally table is another option, however my supervisor says he thinks there should be another way to do it without using one of those either. Any helpful tips or suggestions for this?
Example scenario:
--This will get all possible users
SELECT user
FROM tableUsers
--This will get all of the ID's that do not belong to a given user with userID user from the first query
SELECT id
FROM tableAllIds a
WHERE NOT EXISTS (
SELECT userIds
FROM tableUserIds u
WHERE userID = user
AND a.id = u.id
)
The problem is, I don't quite know how to get the the second query to 'loop' through each entry in the first query without using a loop.
Thanks for any possible help you can provide!
October 21, 2015 at 11:54 am
One way is to CROSS JOIN the users and all possible IDs, and then use NOT EXISTS to only return those possible combinations that don't have a match in the table with actual user/id assignments.
Something like this:DECLARE @tableUsers TABLE ([User] int)
DECLARE @tableAllIds TABLE (ID int)
DECLARE @tableUserIds TABLE (UserID int, ID int)
INSERT INTO @tableUsers
VALUES (1),(2),(3),(4)
INSERT INTO @tableAllIds
VALUES (1),(2),(3),(4),(5),(6)
INSERT INTO @tableUserIds
VALUES (1,1),(1,2),(2,3),(2,5),(3,5),(3,4),(3,6)
SELECT U.[User], AI.ID AS UnassignedID
FROM @tableUsers U CROSS JOIN @tableAllIds AI
WHERE NOT EXISTS(
SELECT NULL
FROM @tableUserIds UI
WHERE UI.ID=AI.ID AND U.[User]=UI.UserID
)
ORDER BY U.[User] ASC, AI.ID ASC
Cheers!
October 21, 2015 at 12:11 pm
This worked perfectly for what I need! Thank you so much!
Regards,
JH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply