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!
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!
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?
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 :-) .
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
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
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
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?
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.
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?
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.
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.
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?
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
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
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
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.
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 :))
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks gadfly and folderol.
sorry for keeping you up so late. i had something tonight and will review it tomorrow morning.
sorry for keeping you up so late. i had something tonight and will review it tomorrow morning.
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.
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
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
Mohammad Pourebtehaj
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.
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 :).
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.
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
it dosent fair .
i think Tom did it and he deserved it :)
by the way thanks for your question :)
Mohammad Pourebtehaj
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))