Need help with a tricky MS SQL Query
-
Hi, I was given a tricky challenge question to attempt and allowed the use of any resources at my disposal. So far all my attempts have produced results close to the expected output but not quiet the expected out put. Here's the link to the question: Question and here's where you can test your query: test here. Here's my query that produced the closest results:
SELECT DISTINCT Competencies.CompName AS compname, Users.FirstName AS firstname, Users.SurName AS surname, CompPos.Level AS positionlevel, CompUser.Level AS userlevel
FROM Positions INNER JOIN CompPos
ON Positions.PosId = CompPos.PosID
INNER JOIN Competencies
ON CompPos.CompID = Competencies.CompID
INNER JOIN CompUser
ON Competencies.CompID = CompUser.CompID
CROSS JOIN Users
WHERE (Users.UserId IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND (CompUser.UserID IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND (Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%')
ORDER BY CompPos.Level DESC, Competencies.CompName, CompUser.Level, Users.FirstName, Users.SurName -
Hi, I was given a tricky challenge question to attempt and allowed the use of any resources at my disposal. So far all my attempts have produced results close to the expected output but not quiet the expected out put. Here's the link to the question: Question and here's where you can test your query: test here. Here's my query that produced the closest results:
SELECT DISTINCT Competencies.CompName AS compname, Users.FirstName AS firstname, Users.SurName AS surname, CompPos.Level AS positionlevel, CompUser.Level AS userlevel
FROM Positions INNER JOIN CompPos
ON Positions.PosId = CompPos.PosID
INNER JOIN Competencies
ON CompPos.CompID = Competencies.CompID
INNER JOIN CompUser
ON Competencies.CompID = CompUser.CompID
CROSS JOIN Users
WHERE (Users.UserId IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND (CompUser.UserID IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND (Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%')
ORDER BY CompPos.Level DESC, Competencies.CompName, CompUser.Level, Users.FirstName, Users.SurNameWebsite wrote:
I only want to see results for users with the following userids '999209','helpdesk1','999339','helpdesk5','999827' Even if they don’t have records in compuser.
"Want"? How about learning SQL, breaking the problem into smaller pieces? ..and no, it's generally not a good idea to have a server open like that; it's obvious that you're running Sql Server, and one can request a list of all the databases in there. Ever heard of Bobby Tables[^]? "All input is evil until proven otherwise".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Hi, I was given a tricky challenge question to attempt and allowed the use of any resources at my disposal. So far all my attempts have produced results close to the expected output but not quiet the expected out put. Here's the link to the question: Question and here's where you can test your query: test here. Here's my query that produced the closest results:
SELECT DISTINCT Competencies.CompName AS compname, Users.FirstName AS firstname, Users.SurName AS surname, CompPos.Level AS positionlevel, CompUser.Level AS userlevel
FROM Positions INNER JOIN CompPos
ON Positions.PosId = CompPos.PosID
INNER JOIN Competencies
ON CompPos.CompID = Competencies.CompID
INNER JOIN CompUser
ON Competencies.CompID = CompUser.CompID
CROSS JOIN Users
WHERE (Users.UserId IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND (CompUser.UserID IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827'))
AND (Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%')
ORDER BY CompPos.Level DESC, Competencies.CompName, CompUser.Level, Users.FirstName, Users.SurNamePlease try this one, if this helps, u might need to do some modification. ============= select Competencies.CompName AS compname, Users.FirstName AS firstname, Users.SurName AS surname, CompPos.Level AS positionlevel, CompUser.Level AS userlevel from Users left outer join compuser on users.userid = compuser.userid left outer join Competencies on compuser.compid = Competencies.compid left outer join CompPos on CompPos.compid = Competencies.compid left outer join Positions on Positions.posid = CompPos.posid and Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%' WHERE Users.UserId IN ('999209', 'helpdesk1', '999339', 'helpdesk5', '999827') --and Positions.Position LIKE '%Corp HR-People Development-Wellness-Manager(Process)%' ORDER BY CompPos.Level DESC, Competencies.CompName, CompUser.Level, Users.FirstName, Users.SurName
-
Website wrote:
I only want to see results for users with the following userids '999209','helpdesk1','999339','helpdesk5','999827' Even if they don’t have records in compuser.
"Want"? How about learning SQL, breaking the problem into smaller pieces? ..and no, it's generally not a good idea to have a server open like that; it's obvious that you're running Sql Server, and one can request a list of all the databases in there. Ever heard of Bobby Tables[^]? "All input is evil until proven otherwise".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
Eddy Vluggen wrote:
..and no, it's generally not a good idea to have a server open like that; it's obvious that you're running Sql Server, and one can request a list of all the databases in there.
From the question I would guess that the OP doesn't own the database server.