SQL question

Hi

I'm quite stumped on this issue.

The data that I have is about people, jobs, and knowledge areas.

1 table lists people and the knowledge areas they indicated so
peopleID  k1  k2  k3  k4  k5  k6 ...k25

a person can select 0 or all 25 knowledges

for the jobs, i have made a new table that lists the top5 knowledge areas for that job
jobID  KnowID  Value
A          k2          4.5
A          k4            3

I can transform that table to be also
jobID  k1   k2  ... k25  and there would only be 5 knowledge columns with values for each row.

What I want to do is select jobs that match the knowledge areas that people have selected.
Where I'm stumped is if a person has more than 5 knowledge areas, how do I see if 5 of like the 10 selected match the top5 for that job.  Somehow I have to figure out all the combinations for what they've selected right?

ANy help woudl be great.  I need to run this for a meeting tomorrow.

Thanks!
rpark2Asked:
Who is Participating?
 
folderolCommented:
I think this will work.  Thanks to gad_fly, as those posts took this 98%.  If this looks good, award the points to gad_fly accordingly.

select
wanted.PeopleID,
wanted.JobID,
wanted.Skills,
wanted.Rank
from
(
select
PEOPLE.PeopleID,
bestjobskills.JobID,
count(bestjobskills.KnowledgeID) as 'Skills',
Sum(bestjobskills.Point) as 'Rank'
from
PEOPLE
join
(
select JobID, KnowledgeID, Point from JOB where KnowledgeID in (select top 5 KnowledgeID from JOB as topjob where topjob.JobID = JOB.JobID order by Point desc)
)
as bestjobskills on PEOPLE.KnowledgeID = bestjobskills.KnowledgeID
group by PEOPLE.PeopleID, JobID
)
as wanted
join
(
select count(KnowledgeID) as 'TotalSkills', PeopleID from PEOPLE group by PeopleID
)
as skillset on wanted.PeopleID = skillset.PeopleID

where wanted.Skills > case when skillset.TotalSkills > 3 then 3 else skillset.TotalSkills end
order by wanted.PeopleID, wanted.Rank


What I did was select the top 5 KnowledgeID per job and build a virtual table called bestjobskills.
If you don't have more than 5 KnowledgeID per JobID then you don't need the where clause so erase that part.
Joining this to PEOPLE, I can expect at most 5 one to many rows returned per person,
representing the best skills for the job.  If a person matches fewer than all 5 skills, the column 'Skills' will tell me.
I call this resultset of rows 'wanted' and I joined it to another virtual table that contains the total knowledgeID count each person selected.  This is so I don't include in the final results people who match only 1 or 2 KnowledgeID on a JobID.

There are a lot of '(' and ')' used here but you can simplify it a lot by assuming no job has more than 5 skills and every person specified a minimum number of skills.  You can hardcode a limit of 3, 4, or 5 KnowledgeID matched into a where clause and remove the subqueries I did.

The order by lists the jobs by highest Points first, so the best job for the person is the ones with the most important skills matched.

Don't know if this is exactly what you needed, just having fun with it :)
...

Tom
0
 
Scott PletcherSenior DBACommented:
This format:

jobID  k1   k2  ... k25

would be much easier to use for this query.


SELECT p.peopleID
FROM people p
CROSS JOIN jobs j
WHERE j.KnowID1 IN (p.k1, p.k2, p.k3, ..., p.k25)
  AND j.KnowID2 IN (p.k1, p.k2, p.k3, ..., p.k25)
  AND j.KnowID3 IN (p.k1, p.k2, p.k3, ..., p.k25)
  AND j.KnowID4 IN (p.k1, p.k2, p.k3, ..., p.k25)
  AND j.KnowID5 IN (p.k1, p.k2, p.k3, ..., p.k25)
--AND j.jobID IN (...specific job(s))
0
 
rpark2Author Commented:
Hi, thanks for your response.

What I want the query to do though it so select jobID where that people's knowledge is in the top 5 of that job.  

if  a person selected less than 5, lilke 3, then where their 3 appear in the top 5 of that job.

How does this change the query you provided?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Scott PletcherSenior DBACommented:
A lot :-) .
0
 
rpark2Author Commented:
So then what would it be?

0
 
gad_flyCommented:
hi rpark2

please help me for your question !!

i think you have table 1 with the composite key by the PeopleID and KnowID ( right?)
and then you have another table (2) for jobs with JobID and KnowID and the value of this Know in this Job .(right?)

and you want to select the high value combination knowID value ( selected by the People ) and suggest the job for them .

if i am right please let me know to help you.

Thanks :)

Mohammad Pourebtehaj
0
 
rpark2Author Commented:
Hi Mohammad

No Table 1 has the columns PeopleID, k1, k2, ...k25
the values for the knowledge are yes/no
And then table 2 has
jobID, k1, k2, ..k25
the values are from 1-5 for the knowledges

But I have already changed the data in Table2 so that for each job, it has the 5 highest knowledges and every other column is blank

What I want to do is recommend jobs for the people based on knowledges that match.

So if person 1 has k2, k3, k4, k20, k26, k27

i want to look at all jobs that have a combination of those 5

I hoep this helps.  It's pretty complicated I think
0
 
gad_flyCommented:

Yes it's very complicated because your table not well designed maybe !!

by the way you say " I need to run this for a meeting tomorrow" and i think you dont have enough time to change the table design with data on it.

ok lets do with this format
0
 
gad_flyCommented:
you say if person 1 had some knowladge you want to search for the right job right?
0
 
rpark2Author Commented:
yes.  if person 1 had certain knowledges of the 25 indicated, i want to find all jobs that have 5 of those knowledges in the top 5 for that job.  (i've already filtered the jobs table to have just the top 5)

i can change the format of the tables too if it's easier.

0
 
gad_flyCommented:
Table 1
PeopleID   K1   K2  K3  K4 ----- K25
1              0     1    0    0          1
2              1     0    1    1          1

Table 2
JobID   K1  K2  K3  K4  -------K25
1          3    4    2   0             4
2          4    1    0   3             5
3          5    0    1   5             0

right?
0
 
gad_flyCommented:
oh yes it's been very easier to like this :

PeopleID and KnowledgeID Are Compsite ( Both Primary Key)

Table 1
PeopleID   KnowledgeID
1               1
1               2
1               4
1               25
2                2
2                24

JobID and KnowledgeID is Compisite KEy
Table 2
JobID     KnowledgeID   Point
1                1                  4
1                2                  0
1                3                   2


ok.


0
 
rpark2Author Commented:
hi

the tables you drew out are correct

for the job table, I actually have it
JobID, knowID  point

I can covert ther other one.

if I do that, what query can i run?
0
 
gad_flyCommented:

for this format you must write tow query

first calculate total job point for people then select top 5  job for each people


i working on it just gave me some time .

0
 
gad_flyCommented:
select  a.JobID  ,  b.KnowledgeID  , sum (a.Point ) as JobPoint
  from  
         job  a  inner join  People b
on  a.KnowledgeID = b.KnowledgeID
where b.PeopleID = @People

0
 
gad_flyCommented:
and then use this

 select top 5 jobpoint from view1  
order by jobpoint desc
0
 
gad_flyCommented:
sorry

change the first view to this  

SELECT     SUM(b.point) AS JobPoint, b.jobid
FROM         People a
 INNER JOIN
                      job  b ON a.knowledgeid = b.knowledgeid
WHERE     (a.PeopelID = @PeopelID)
GROUP BY b.jobid

i hope it's usefull

Mohammad Pourebtehaj
0
 
gad_flyCommented:
you can give the @peopleID for now  number ( like 1 !!)  and then when you have time create simple form with C# for it to give the query @peopleID
and see the resault.

0
 
gad_flyCommented:
Is It Usefull ?
 do you want any help ?

cause i want to go to sleep ( in my time its 4 in morning and i was going to work at 8 !!! :)) )

I wait for response and then bye :))
0
 
rpark2Author Commented:
thanks gadfly and folderol.

sorry for keeping you up so late.  i had something tonight and will review it tomorrow morning.

0
 
rpark2Author Commented:
Hi folderol,

I tried running this in Query Analyzer of MS SQL but I'm getting some errors.

Like the ORDER BY clause is invalid in view, inline functions, derived tables, subqueries unless TOP ias also specified.
0
 
folderolCommented:
That is correct.  Delete the last line
ORDER BY wanted.PeopleID, wanted.Rank

and that should work.
There is only the one other ORDER BY and that has a TOP.  By the way, do you even need that?  That is technically a subquery, these are not the best code from a performance standpoint.  You could shorten it to

(
select JobID, KnowledgeID, Point from JOB
)
as bestjobskills ... etc

Also, yesterday I created dummy data in an SQL 2000 database and the code I provided ran without any errors.

If you are making a view, when you reference the view you can apply the order by like this:

select * from view order by col_1

Tom
0
 
gad_flyCommented:
hi Tom and  very good job

Mohammad Pourebtehaj
0
 
rpark2Author Commented:
Hi Tom & Mohammed

I got rid of all the errors now, I'm just double checkign to make sure it's doing what I want to do.

I guess for the people who indicated <5 skills, I'll just have to deal with the data by itself.

You guys have really saved me.
0
 
folderolCommented:
Hi Mohammad, thanks, and good job to you too.  Sorting out the data table structure was the hard part :).

0
 
rpark2Author Commented:
Great it worked, I think i'll split the point half and half does that sound fair?

How do I do that, i really haven't used this site much.
0
 
gad_flyCommented:
hi rpark2 :)

it dosent fair .

i think Tom did it and he deserved it :)

by the way thanks for your question :)

Mohammad Pourebtehaj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.