June 26, 2014 at 10:33 am
It looked like a simple question, but I don't know how to get the answer.
I need to get the user who can access all the color.
That means the result is userid 3.
CREATE TABLE Color (colorid INT, NAME VARCHAR(100))
INSERT INTO Color(Colorid, Name)
SELECT 1, 'Black'
UNION
SELECT 2, 'White'
UNION
SELECT 3, 'Blue'
UNION
SELECT 4, 'Green'
UNION
SELECT 5, 'Red'
CREATE TABLE User (Userid INT, NAME VARCHAR(100))
Insert Into User (Userid, Name)
SELECT 1, 'Bill'
UNION
SELECT 2, 'Jim'
UNION
SELECT 3, 'Tom'
CREATE TABLE UserColor (Userid INT, Colorid INT)
INSERT INTO UserColor(Userid, Colorid)
SELECT 1, 1
UNION
SELECT 1, 2
UNION
SELECT 2, 1
UNION
SELECT 2, 2
UNION
SELECT 2, 3
UNION
SELECT 3, 1
UNION
SELECT 3, 2
UNION
SELECT 3, 3
UNION
SELECT 3, 4
UNION
SELECT 3, 5
June 26, 2014 at 10:52 am
SELECT UserID FROM UserColor GROUP BY UserID HAVING COUNT(*) = (SELECT COUNT(*) FROM Color);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 11:01 am
I kind of figured it out too. More complicated.
SELECT a.userid
FROM (SELECT u.*,
ROW_NUMBER() OVER(PARTITION BY u.userid ORDER BY u.colorid ) AS Row
FROM UserColor u ) a
INNER JOIN (SELECT COUNT(*) cnt FROM Color) c on a.row = c.cnt
It was an interview question and I got stuck at the interview. Needless to say, I did not get the job.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply