Tricky query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tricky query

Author

Topic

stxlee
Starting Member

3 Posts

Posted - 2010-03-24 : 22:26:28

Hi all,

I'm stuck, can you please help me write this query? Here's the logic:

I have three tables below. It's for a human resources system that matches people to the roles that they request. There's also a master table of predefined job functions that is used to populate a drop-down where they choose the function or category where their requested role fits. For example, a user enter in their roles into a varchar field with a description "Java Software Engineer" and select "IT - Development" from the list. They can enter more than one role.

Management can then keyword search for employees who have entered their requests. What I need to do is to be able to do a full text search for keywords against the roledesc field on the requestedrole table and return list of unique users and the associated user data from all three tables.

users
-----
userId PK
name
contact
location
experience

jobfunctions (Contains basic company roles finance, it, sales, etc.)
------------
jfid PK
functiondesc

requestedrole
-----------
roleid PK
jfid FK
userid FK
roledesc (varchar)

This is secondary, but I also need to be able to implement a search filter (like ebay, etc) on the left where I can show relevant filters (jobfunction, location, etc.) and management can use to zero in on prospective candidates. How do I do a count of the jobfunctions? Let's say I'm a manager and I enter "Engineer", I'd like to be able to see on the left side categories and subcategories with the count of the users that match listed (e.g. Software Development(3), Civil Engineer(4), Electrical(7) and so on). The counts are clickable, and if they choose "Software Development(3)", then there is another query that returns the 3 that matches.

I know I am asking a lot. Please help in any way!

And this is SQL Server 2005...

Thanks!!!!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 02:19:06

You should explain entire module in one shot rather than
go one by one like ask for the query for specific functionality
like how you choose jobfunctions and then what is your expected output etc.

Vaibhav T

stxlee
Starting Member

3 Posts

Posted - 2010-03-25 : 09:10:44

Sure, I'll try. Please let me know if I'm explaining it correctly. Thanks.

users
-----
uid int, PK
name varchar(50)
email varchar(50)
location varchar(50)
exp tinyint
salary int

jobfunctions (Contains basic company roles finance, it, sales, etc.)
------------
jid int PK
funcdesc varchar(50)

requestedrole
-----------
id PK
jid FK
uid FK
roledesc varchar(100)

insert into users(uid,name,email,location,exp,salary) values(1,emp1,a@com,city1,17,50)
insert into users(uid,name,email,location,exp,salary) values(2,emp2,b@com,city2,10,25)
insert into users(uid,name,email,location,exp,salary) values(3,emp3,c@com,city3,6,10)
insert into users(uid,name,email,location,exp,salary) values(4,emp4,d@com,city3,2,11)
insert into users(uid,name,email,location,exp,salary) values(5,emp5,e@com,city4,5,35)

insert into jobfunctions(jid,funcdesc) values(1,sales)
insert into jobfunctions(jid,funcdesc) values(2,it)
insert into jobfunctions(jid,funcdesc) values(3,finance)
insert into jobfunctions(jid,funcdesc) values(4,ops)

insert into requestedrole(rid,jid,uid,roledesc) values(1,1,1,"Regional Head of sales")
insert into requestedrole(rid,jid,uid,roledesc) values(2,2,2,"Java software engineer")
insert into requestedrole(rid,jid,uid,roledesc) values(3,2,2,"SEO Expert")
insert into requestedrole(rid,jid,uid,roledesc) values(4,3,3,"CPA")
insert into requestedrole(rid,jid,uid,roledesc) values(5,3,3,"Controller")
insert into requestedrole(rid,jid,uid,roledesc) values(6,2,4,"Junior .NET engineer")
insert into requestedrole(rid,jid,uid,roledesc) values(7,4,5,"Civil engineer")

Expected output query one - keyword "engineer" (sort ASC salary unique users):
name, email,location,exp,salary,funcdesc,roledesc
--------------------------------------------------
emp4,d@com,city3,2,11,it,Junior vb engineer
emp2,b@com,city2,10,25,it,Java software engineer
emp5,e@com,city4,5,35,ops,Civil engineer

Expected output query two (count for unique uid in each jobfunction for above result)

funcdesc, count
---------------
it,2
ops,1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 10:43:31

query 1


Select u.name, u.email,u.location,u.exp,u.salary,jf.funcdesc,rr.roledesc
from users u
join requestedrole rr
on rr.uid = rr.uid
join jobfunctions jf
on jf.jid=rr.jid
where rr.roledesc LIKE 'Engineer%'
order by  u.salary ASC

query 2

Select jf.funcdesc,count(distinct rr.uid)
from requestedrole rr
join jobfunctions jf
on jf.jid=rr.jid
group by jf.funcdesc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stxlee
Starting Member

3 Posts

Posted - 2010-03-26 : 17:04:09

Thank you! That is close enough to what I need. On the first query, I think you mean "on rr.uid = u.uid" but it works otherwise.

On the second query, it returns all counts, which is a start. But what if I need the count of the result set from the first query?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 02:14:48

make it same as first query then


Select jf.funcdesc,count(distinct rr.uid)
from users u
join requestedrole rr
on rr.uid = rr.uid
join jobfunctions jf
on jf.jid=rr.jid
where rr.roledesc LIKE 'Engineer%'
group by jf.funcdesc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources