May 18, 2016 at 6:38 pm
In our facilities table we have multiple facilities.
AMB
KFC
KHC
KPIC
A person can be assigned to multiple facilities example
(AMB and KFH),
AMB Jon Doe
KFH Jon Doe
KHC Jon Doe
how would I query just those people who are assigned to just the AMB facility?
SELECT
FAC,
LAST,
FIRST
FROM USER
WHERE FAC IN('AMB');
Thanks in advance.
May 18, 2016 at 7:53 pm
how would I query just those people who are assigned to just the AMB facility? --- kinda contradicts your subject question... but anyway...
If you're filtering for a single value, you can use
WHERE MyField = 'Some Value'
If you're filtering for several values...
WHERE MyField IN ('String1', 'String2', 'String3')
May 18, 2016 at 8:15 pm
This is where some DDL and sample data would be helpful. Note the link in my signature for tips on getting help.
Let's say you had a users table and a facUsers table with the data below... I have prepared two techniques
-- Sample Data
DECLARE @users TABLE (UserID int identity, [First] varchar(50), [Last] varchar(50));
DECLARE @FacUsers TABLE (UserID int, Fac VARCHAR(10));
INSERT @users([first],[last]) VALUES ('mr','x'),('Sally','Smith'),('sir','zzz'),('John','Doe');
INSERT @FacUsers VALUES (1,'AMB'),(1,'KFC'),(2,'AMB'),(3,'KPIC'),(3,'KHC'),(4,'KFC'); -- only use 2 qualifies
-- Method #1: First get people assigned to one facility then self-join where fac = 'AMB'
SELECT fac, u.[First], u.[Last]
FROM
(
SELECT fu.UserID
FROM @FacUsers fu
GROUP BY fu.UserID
HAVING COUNT(*) = 1
) f1
JOIN @FacUsers f2
ON f1.UserID = f2.UserID AND f2.Fac = 'AMB'
JOIN @users u ON f2.UserID = u.UserID;
-- Method #2 (much better choice): Get users assigned to 'AMB' who are not assigned to a fac <> 'AMB'
SELECT fac, u.[First], u.[Last]
FROM @FacUsers fu1
JOIN @users u ON fu1.UserID = u.UserID
WHERE Fac = 'AMB'
AND NOT EXISTS
(
SELECT fu2.userID
FROM @FacUsers fu2
WHERE Fac <> 'AMB' AND fu1.UserID = fu2.UserID
);
Note, the second one is the much better choice
-- Itzik Ben-Gan 2001
May 18, 2016 at 8:17 pm
pietlinden (5/18/2016)
how would I query just those people who are assigned to just the AMB facility? --- kinda contradicts your subject question... but anyway...If you're filtering for a single value, you can use
WHERE MyField = 'Some Value'If you're filtering for several values...
WHERE MyField IN ('String1', 'String2', 'String3')
how would I query just those people who are assigned to just the AMB facility?
If I understand the OP he wanted people assigned to 'AMB' and nothing else.
-- Itzik Ben-Gan 2001
May 19, 2016 at 12:29 pm
Alan thank you very much for your reply I was able to get the query working based on what you provided below.
-- Method #2 (much better choice): Get users assigned to 'AMB' who are not assigned to a fac <> 'AMB'
SELECT fac, u.[First], u.[Last]
FROM @FacUsers fu1
JOIN @users u ON fu1.UserID = u.UserID
WHERE Fac = 'AMB'
AND NOT EXISTS
(
SELECT fu2.userID
FROM @FacUsers fu2
WHERE Fac <> 'AMB' AND fu1.UserID = fu2.UserID
);
May 23, 2016 at 2:46 pm
Np
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply