comparing two queries for multiple matching data (1 Viewer)

mackyrm · Sep 26, 2017

I have two sets of queries. A client query that lists the client and their skills (a client can have several lines as the skill comes from a child table where the client is in the parent table). I have a job query, that lists employers and the skills that they are looking for (employer = parent, skills = child, similar to the above).

What would be the best way of finding a match on these two sets of query records, but favouring the job, to find those clients that match the job skill criteria?

e.g. one job may have 3 or 5 or 7 skill entries and I would look for a complete match on those requirements, but a client have many more skills than that and would be suitable if they satisfy the criteria 3/5/7 criteria as set by the employer, whatever that may be.

I could probably do this in VBA but wanted to see if there is a simpler and more elegant SQL solution.

Thanks

plog · Sep 26, 2017

Can you demonstrate your issue with data? Provide 2 sets:

A. Starting sample data from tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you hope to end up with when you feed this new query the data from A.

mackyrm · Sep 26, 2017

Query 1 output, client skills, looks like this

CLIENT | SKILL
--------- -----------
Joe Bloggs | Psychology
Joe Bloggs | Biology
Joe Bloggs | Public Speaking
Jane Smith | Biology
Jane Smith | Biochemistry
Jane Smith | Media
Jane Smith | Public Speaking
Alice Wonderland | Biology
Alice Wonderland | Biochemistry
Alice Wonderland | Media

Query 2, the basis for selecting a client looks like this
SKILL | JOB
----------- ----------
Biology | Job1
Biochemistry | Job1
Media | Job1

The output in this case, based on a comparison of the outputs, would be Jane Smith and Alice Wonderland. No need for skills to be outputted, just a distinct client output where all the criteria from the job-skill record is found within a client-skill record output.

Sorry about the formatting. Bar means column break (tab doesn't work in the editor)

plog · Sep 26, 2017

This is going to take 2 subqueries, then a main query to get your final data. Here's the SQL of the first:

Code:

SELECT Query2.JOB, Count(Query2.SKILL) AS TotalSkills
FROM Query2
GROUP BY Query2.JOB

Paste that into a new query and call it 'JobMatch_sub1'. It gets the total number of skills for each job in Query2.

Code:

 SELECT Query1.CLIENT, Query2.JOB, Count(Query1.SKILL) AS Skill_Total
FROM Query1 INNER JOIN Query2 ON Query1.SKILL = Query2.SKILL
GROUP BY Query1.CLIENT, Query2.JOB;

Paste the above into a new query and call it 'JobMatch_sub2'. It gets the number of skill matches between people and jobs. Then, to get your final result, use the below SQL:

Code:

SELECT JobMatch_sub2.CLIENT, JobMatch_sub2.JOB
FROM JobMatch_sub2 INNER JOIN JobMatch_sub1 ON (JobMatch_sub2.JOB = JobMatch_sub1.JOB) AND (JobMatch_sub2.Skill_Total = JobMatch_sub1.TotalSkills);

comparing two queries for multiple matching data (1 Viewer)

mackyrm

Registered User.

plog

Banishment Pending

mackyrm

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread