how to get the records based on skill collection

Question

1.00/5 (2 votes)

See more:

I have requirement in Sql Server

My First Table having SkillSet Like .net,mvc,jquery in one of records

and in My Profile Table iam having candidate skillset like mvc/mvc,jquery
based on above value .net,mvc,jquery we have to get the records of profile table..

how can i solve this problem

Pleas help me

Regards,
Syed Khaleel

Posted 31-Dec-13 1:53am

syedkhaleel

Add a Solution

Comments

Peter Leow 1-Jan-14 0:34am

Not enough information. Please provide database schema. Use the "Improve question" button.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

♥…ЯҠ…♥ · Answer 1 · 2013-12-31T02:01:00

Solution 1

Hi Syed,

You could prefer UNION operator[^] to do this.
Like

SQL

SELECT * FROM MyProfileTable WHERE candidateSkillset IN 
( SELECT candidateSkillset FROM MyProfileTable
UNION
SELECT SkillSet FROM SkillSetTable )

Hope this helps you a bit.

Regards,
RK

Posted 31-Dec-13 2:01am

♥…ЯҠ…♥

Updated 31-Dec-13 2:27am

v3

Comments

[no name] 31-Dec-13 8:18am

based on requiremnet skills we need to fetch profile records which match skill of requirement.

♥…ЯҠ…♥ 31-Dec-13 8:23am

Use the above query as sub query, using this query get Profile details. By executing the above query what you get?

♥…ЯҠ…♥ 31-Dec-13 8:27am

updated my solution, now try and lemme know

Christian Graus 31-Dec-13 19:59pm

I'm sorry, but this is only going to work if the skillsets are identical. And even then, it's wildly inefficient

Christian Graus · Answer 2 · 2013-12-31T13:54:00

The union query is stupid. So is using a subquery. If the skills are in a single string, as in one cell is .net, MVC, jquery, then your database design is crap and needs fixing. You need a table like this:

MyProfileTable
(
ProfileId int,
... whatever else
)

And one like this :

candidateSkillset
(
skillsetId int
)

and one like this:

create table skill
(
skillId int identity,
skill varchar(50)
)

Then one like this:

CandidateSkills
(
skillId int,
candidateId int
)

and one like this:

SkillsetSkill
(
skillsetId int,
SkillId int
)

Then you would do a join where skillsetskill.skillid = candidateskills.skillid.

If you need more info, start by posting the schema of your tables and some sample data. If your database is as broken as I suspect and you can't change it, it can still be done, but it will always be bad.