Database Discussion Boards

View All Threads

First Prev Next

Need help with a tricky MS SQL Query

muya mwansa27-Nov-12 6:46

muya mwansa

27-Nov-12 6:46

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

Re: Need help with a tricky MS SQL Query

Eddy Vluggen27-Nov-12 13:31

Eddy Vluggen

27-Nov-12 13:31

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: Need help with a tricky MS SQL Query

jschell28-Nov-12 8:43

jschell

28-Nov-12 8:43

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.

Re: Need help with a tricky MS SQL Query

Deepak Kr128-Nov-12 1:03

Deepak Kr1

28-Nov-12 1:03

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

Last Visit: 31-Dec-99 18:00 Last Update: 15-Apr-24 22:34

Refresh

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.