[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL question

Posted on 2006-03-28
27
Medium Priority
?
221 Views
Last Modified: 2011-10-03
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!
0
Comment
Question by:rpark2
  • 13
  • 9
  • 3
  • +1
27 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16315693
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
 

Author Comment

by:rpark2
ID: 16315792
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16315873
A lot :-) .
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rpark2
ID: 16315974
So then what would it be?

0
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316074
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
 

Author Comment

by:rpark2
ID: 16316145
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316205

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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316214
you say if person 1 had some knowladge you want to search for the right job right?
0
 

Author Comment

by:rpark2
ID: 16316255
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316283
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316320
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
 

Author Comment

by:rpark2
ID: 16316327
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316391

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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316449
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316482
and then use this

 select top 5 jobpoint from view1  
order by jobpoint desc
0
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316540
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316568
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16316583
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
 
LVL 19

Accepted Solution

by:
folderol earned 2000 total points
ID: 16317376
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
 

Author Comment

by:rpark2
ID: 16318491
thanks gadfly and folderol.

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

0
 

Author Comment

by:rpark2
ID: 16324731
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
 
LVL 19

Expert Comment

by:folderol
ID: 16325031
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16325136
hi Tom and  very good job

Mohammad Pourebtehaj
0
 

Author Comment

by:rpark2
ID: 16325158
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
 
LVL 19

Expert Comment

by:folderol
ID: 16325220
Hi Mohammad, thanks, and good job to you too.  Sorting out the data table structure was the hard part :).

0
 

Author Comment

by:rpark2
ID: 16326391
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
 
LVL 2

Expert Comment

by:gad_fly
ID: 16326450
hi rpark2 :)

it dosent fair .

i think Tom did it and he deserved it :)

by the way thanks for your question :)

Mohammad Pourebtehaj
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question