|
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
|
|
|
|
|
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".
|
|
|
|
|
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.
|
|
|
|
|
Please 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
|
|
|
|