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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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))
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?
Scott PletcherSenior DBACommented:
A lot :-) .
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

rpark2Author Commented:
So then what would it be?

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
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
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
gad_flyCommented:
you say if person 1 had some knowladge you want to search for the right job right?
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.

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?
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.


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?
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 .

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

gad_flyCommented:
and then use this

 select top 5 jobpoint from view1  
order by jobpoint desc
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
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.

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 :))
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rpark2Author Commented:
thanks gadfly and folderol.

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

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.
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
gad_flyCommented:
hi Tom and  very good job

Mohammad Pourebtehaj
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.
folderolCommented:
Hi Mohammad, thanks, and good job to you too.  Sorting out the data table structure was the hard part :).

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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.