rlarian
asked on
MS-SQL join queries based on common (not exact) results
Query1 returns names and skills, Query2 returns jobs and required skills
Query1
Name1 skill1
Name2 skill1
Name2 skill2
Name3 skill2
Query2
Job1 skill1
Job2 skill1
Job2 skill2
Job3 skill2
The desired final result would be a list of names and the jobs
Name1 Job1
Name2 Job1
Name2 Job2
Name2 Job3
Name3 Job3
Query1
Name1 skill1
Name2 skill1
Name2 skill2
Name3 skill2
Query2
Job1 skill1
Job2 skill1
Job2 skill2
Job3 skill2
The desired final result would be a list of names and the jobs
Name1 Job1
Name2 Job1
Name2 Job2
Name2 Job3
Name3 Job3
ASKER
problem is where Name has one of the skills of Job, it shows up in the results. I need the Name to match to a Job only if they have all the skills of the Job.
IF OBJECT_ID('tempdb..#names' ) IS NOT NULL DROP TABLE #names
CREATE TABLE #names(
name varchar(30),
skill int)
IF OBJECT_ID('tempdb..#jobs') IS NOT NULL DROP TABLE #jobs
CREATE TABLE #jobs(
job varchar(30),
skill int)
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2
SELECT Q1.name, Q2.job
FROM (SELECT * FROM #names) Q1, (SELECT * FROM #jobs) Q2
WHERE Q1.skill = Q2.skill
ORDER BY Q1.name
DROP TABLE #names
DROP TABLE #jobs
RESULTS:
name1 job1
name1 job2 [should not match]
name2 job1
name2 job2 [should only match once]
name2 job2 [should only match once]
name2 job3
name3 job2 [should not match]
name3 job3
IF OBJECT_ID('tempdb..#names'
CREATE TABLE #names(
name varchar(30),
skill int)
IF OBJECT_ID('tempdb..#jobs')
CREATE TABLE #jobs(
job varchar(30),
skill int)
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2
SELECT Q1.name, Q2.job
FROM (SELECT * FROM #names) Q1, (SELECT * FROM #jobs) Q2
WHERE Q1.skill = Q2.skill
ORDER BY Q1.name
DROP TABLE #names
DROP TABLE #jobs
RESULTS:
name1 job1
name1 job2 [should not match]
name2 job1
name2 job2 [should only match once]
name2 job2 [should only match once]
name2 job3
name3 job2 [should not match]
name3 job3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You did not mention your SQL version. I hope you are working on 2005 or later. If so, you can try this query.
;with names as (
select distinct t1.name,rtrim(substring(isnull((select ','+convert(varchar,t2.skill) from #names t2 where t1.name = t2.name for xml path('')),' '),2,2000)) skill
from #names t1)
,jobs as (
select distinct t1.job,rtrim(substring(isnull((select ','+convert(varchar,t2.skill) from #jobs t2 where t1.job = t2.job for xml path('')),' '),2,2000)) skill
from #jobs t1)
select n.name,j.job
from names n
join jobs j on n.skill like '%'+j.skill+'%'
IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names
CREATE TABLE #names(
name varchar(30),
skill int)
IF OBJECT_ID('tempdb..#jobs') IS NOT NULL DROP TABLE #jobs
CREATE TABLE #jobs(
job varchar(30),
skill int)
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2
;with names as (
select distinct t1.name,rtrim(substring(isnull((select ','+convert(varchar,t2.skill) from #names t2 where t1.name = t2.name for xml path('')),' '),2,2000)) skill
from #names t1)
,jobs as (
select distinct t1.job,rtrim(substring(isnull((select ','+convert(varchar,t2.skill) from #jobs t2 where t1.job = t2.job for xml path('')),' '),2,2000)) skill
from #jobs t1)
select n.name,j.job
from names n
join jobs j on n.skill like '%'+j.skill+'%'
DROP TABLE #names
DROP TABLE #jobs
/*
name job
name1 job1
name2 job1
name2 job2
name2 job3
name3 job3
*/
ASKER
SQL 2008R2
if i enter extra jobs, not in order, it doesn't return the correct result. job 5 below inserted with skill 2, 1, 4 will not match name2. if is inserted as 1,2,4 it will match.
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name2', 4 UNION ALL
SELECT 'name2', 5 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2 UNION ALL
SELECT 'job4', 3 UNION ALL
SELECT 'job5', 2 UNION ALL --
SELECT 'job5', 1 UNION ALL --
SELECT 'job5', 4
if i enter extra jobs, not in order, it doesn't return the correct result. job 5 below inserted with skill 2, 1, 4 will not match name2. if is inserted as 1,2,4 it will match.
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name2', 4 UNION ALL
SELECT 'name2', 5 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2 UNION ALL
SELECT 'job4', 3 UNION ALL
SELECT 'job5', 2 UNION ALL --
SELECT 'job5', 1 UNION ALL --
SELECT 'job5', 4
You can't be running the code I posted because insert order has no effect on the query I gave. With you new data I get the following (which I believe is correct) regardless of what order I insert it.
name1 job1
name2 job1
name2 job2
name2 job3
name2 job5
name3 job3
name1 job1
name2 job1
name2 job2
name2 job3
name2 job5
name3 job3
ASKER
here is the code i'm running. i thought it was a bit strange.
IF OBJECT_ID('tempdb..#names' ) IS NOT NULL DROP TABLE #names
CREATE TABLE #names(
name varchar(30),
skill int)
IF OBJECT_ID('tempdb..#jobs') IS NOT NULL DROP TABLE #jobs
CREATE TABLE #jobs(
job varchar(30),
skill int)
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name2', 4 UNION ALL
SELECT 'name2', 5 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2 UNION ALL
SELECT 'job4', 3 UNION ALL
SELECT 'job5', 2 UNION ALL
SELECT 'job5', 1 UNION ALL
SELECT 'job5', 4
;with names as (
select distinct t1.name,rtrim(substring(is null((sele ct ','+convert(varchar,t2.ski ll) from #names t2 where t1.name = t2.name for xml path('')),' '),2,2000)) skill
from #names t1)
,jobs as (
select distinct t1.job,rtrim(substring(isn ull((selec t ','+convert(varchar,t2.ski ll) from #jobs t2 where t1.job = t2.job for xml path('')),' '),2,2000)) skill
from #jobs t1)
select n.name,j.job
from names n
join jobs j on n.skill like '%'+j.skill+'%'
DROP TABLE #names
DROP TABLE #jobs
IF OBJECT_ID('tempdb..#names'
CREATE TABLE #names(
name varchar(30),
skill int)
IF OBJECT_ID('tempdb..#jobs')
CREATE TABLE #jobs(
job varchar(30),
skill int)
INSERT INTO #names
SELECT 'name1', 1 UNION ALL
SELECT 'name2', 1 UNION ALL
SELECT 'name2', 2 UNION ALL
SELECT 'name2', 4 UNION ALL
SELECT 'name2', 5 UNION ALL
SELECT 'name3', 2
INSERT INTO #jobs
SELECT 'job1', 1 UNION ALL
SELECT 'job2', 1 UNION ALL
SELECT 'job2', 2 UNION ALL
SELECT 'job3', 2 UNION ALL
SELECT 'job4', 3 UNION ALL
SELECT 'job5', 2 UNION ALL
SELECT 'job5', 1 UNION ALL
SELECT 'job5', 4
;with names as (
select distinct t1.name,rtrim(substring(is
from #names t1)
,jobs as (
select distinct t1.job,rtrim(substring(isn
from #jobs t1)
select n.name,j.job
from names n
join jobs j on n.skill like '%'+j.skill+'%'
DROP TABLE #names
DROP TABLE #jobs
ASKER
Dale, just noticed i had two different solutions... checking yours
You're doing it quite different from what I suggested. You're building a string of concatenated skills per person and per job and then trying to match them. Of course order matters then because if you look at the intermediate results (below) the name has a computed string '1,2,4' but the job has '2,1,4'. You can solve that by ordering the query where you compute the string e.g. #names t2 where t1.name = t2.name order by Skill for xml path" but you've over-complicated it for my liking.
name1 1 job1 1
name1 1 job2 1,2
name1 1 job3 2
name1 1 job4 3
name1 1 job5 2,1,4
name2 1,2,4,5 job1 1
name2 1,2,4,5 job2 1,2
name2 1,2,4,5 job3 2
name2 1,2,4,5 job4 3
name2 1,2,4,5 job5 2,1,4
name3 2 job1 1
name3 2 job2 1,2
name3 2 job3 2
name3 2 job4 3
name3 2 job5 2,1,4
name1 1 job1 1
name1 1 job2 1,2
name1 1 job3 2
name1 1 job4 3
name1 1 job5 2,1,4
name2 1,2,4,5 job1 1
name2 1,2,4,5 job2 1,2
name2 1,2,4,5 job3 2
name2 1,2,4,5 job4 3
name2 1,2,4,5 job5 2,1,4
name3 2 job1 1
name3 2 job2 1,2
name3 2 job3 2
name3 2 job4 3
name3 2 job5 2,1,4
ASKER
Dale, there are actually 5 tables. person, personSkillMap, skill, jobSkillMap, job
job can can have many skills and a person must have all the skills to match the job.
yours is doing what i need to get me in the right direction. thanks for the help.
job can can have many skills and a person must have all the skills to match the job.
yours is doing what i need to get me in the right direction. thanks for the help.
ASKER
Dale, thanks again. here is my 5 table version.
declare @Name table (ID int, Name varchar(30), NameSkillMap int)
declare @Job table (ID int, Job varchar(30), JobSkillMap int)
declare @Skill table (ID int, Skill varchar(30))
declare @NameSkillMap table (NameID int, SkillID int)
declare @JobSkillMap table (JobID int, SkillID int)
insert into @Name (ID, Name, NameSkillMap)
select 1, 'name1', 1 UNION ALL
select 2, 'name2', 2 UNION ALL
select 3, 'name3', 3 UNION ALL
select 4, 'name4', 4
insert into @Job (ID, Job, JobSkillMap)
select 1, 'job1', 1 UNION ALL
select 2, 'job2', 2 UNION ALL
select 3, 'job3', 3 UNION ALL
select 4, 'job4', 4
insert into @Skill (ID,Skill)
select 1, 'skill1' UNION ALL
select 2, 'skill2' UNION ALL
select 3, 'skill3' UNION ALL
select 4, 'skill4'
insert into @NameSkillMap (NameID, SkillID)
select 1, 1 UNION ALL
select 2, 2 UNION ALL
select 3, 1 UNION ALL
select 3, 2 UNION ALL
select 3, 3 UNION ALL
select 3, 4 UNION ALL
select 4, 1 UNION ALL
select 4, 4
insert into @JobSkillMap (JobID, SkillID)
select 1, 1 UNION ALL
select 1, 2 UNION ALL
select 2, 1 UNION ALL
select 2, 2 UNION ALL
select 2, 3 UNION ALL
select 2, 4 UNION ALL
select 3, 2 UNION ALL
select 4, 4
select n.Name, j.Job
from @Job J, @Name N
where not exists (
select 1
from @Job J1
join @JobSkillMap JSM on JSM.JobID = J1.ID
join @Skill SJ on SJ.ID = JSM.SkillID
where J1.Job = J.Job
and not exists (select 1 from @Name N1
join @NameSkillMap NSM on NSM.NameID = N1.ID
join @Skill S on S.ID = NSM.SkillID
where N1.Name = N.Name and S.Skill = SJ.Skill)
)
order by N.Name, J.Job
declare @Name table (ID int, Name varchar(30), NameSkillMap int)
declare @Job table (ID int, Job varchar(30), JobSkillMap int)
declare @Skill table (ID int, Skill varchar(30))
declare @NameSkillMap table (NameID int, SkillID int)
declare @JobSkillMap table (JobID int, SkillID int)
insert into @Name (ID, Name, NameSkillMap)
select 1, 'name1', 1 UNION ALL
select 2, 'name2', 2 UNION ALL
select 3, 'name3', 3 UNION ALL
select 4, 'name4', 4
insert into @Job (ID, Job, JobSkillMap)
select 1, 'job1', 1 UNION ALL
select 2, 'job2', 2 UNION ALL
select 3, 'job3', 3 UNION ALL
select 4, 'job4', 4
insert into @Skill (ID,Skill)
select 1, 'skill1' UNION ALL
select 2, 'skill2' UNION ALL
select 3, 'skill3' UNION ALL
select 4, 'skill4'
insert into @NameSkillMap (NameID, SkillID)
select 1, 1 UNION ALL
select 2, 2 UNION ALL
select 3, 1 UNION ALL
select 3, 2 UNION ALL
select 3, 3 UNION ALL
select 3, 4 UNION ALL
select 4, 1 UNION ALL
select 4, 4
insert into @JobSkillMap (JobID, SkillID)
select 1, 1 UNION ALL
select 1, 2 UNION ALL
select 2, 1 UNION ALL
select 2, 2 UNION ALL
select 2, 3 UNION ALL
select 2, 4 UNION ALL
select 3, 2 UNION ALL
select 4, 4
select n.Name, j.Job
from @Job J, @Name N
where not exists (
select 1
from @Job J1
join @JobSkillMap JSM on JSM.JobID = J1.ID
join @Skill SJ on SJ.ID = JSM.SkillID
where J1.Job = J.Job
and not exists (select 1 from @Name N1
join @NameSkillMap NSM on NSM.NameID = N1.ID
join @Skill S on S.ID = NSM.SkillID
where N1.Name = N.Name and S.Skill = SJ.Skill)
)
order by N.Name, J.Job
Nice one - thats more what I expected - its working I assume?
ASKER
working like a champ. thanks for the help.
I have tried grouping all skills as a set with XML. An "ORDER BY" is needed for XML solution to work.
But I think Dale has nailed it with simple and elegant approach. Nice work Dale.
But I think Dale has nailed it with simple and elegant approach. Nice work Dale.
Open in new window