Link to home
Start Free TrialLog in
Avatar of rpark2
rpark2

asked on

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!
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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))
Avatar of rpark2
rpark2

ASKER

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?
A lot :-) .
Avatar of rpark2

ASKER

So then what would it be?

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
Avatar of rpark2

ASKER

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

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

ASKER

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.

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


Avatar of rpark2

ASKER

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?

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 .

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

and then use this

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

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 :))
ASKER CERTIFIED SOLUTION
Avatar of folderol
folderol

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rpark2

ASKER

thanks gadfly and folderol.

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

Avatar of rpark2

ASKER

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

Mohammad Pourebtehaj
Avatar of rpark2

ASKER

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

Avatar of rpark2

ASKER

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.
hi rpark2 :)

it dosent fair .

i think Tom did it and he deserved it :)

by the way thanks for your question :)

Mohammad Pourebtehaj