1

Topic: Help to refine request

SELECT u. *, a.* FROM User u
JOIN Applicant an ON a.id = u.id
WHERE u. Role_id = ' 3'
AND exists (select * from ApplicantArea where Applicant_id = u.id AND ProfessionalArea_id = ' 10 ')
AND exists (select * from ApplicantArea where Applicant_id = u.id AND ProfessionalArea_id = ' 13 ')
AND exists (select * from ApplicantRegion where Applicant_id = u.id AND (Region_id = ' 1 ' OR Region_id = ' 4 '))
AND exists (select * from ApplicantSkill where Applicant_id = u.id AND (Skill_id = ' 1 ' AND experience> = ' 1 '))
AND exists (select * from ApplicantSkill where Applicant_id = u.id AND (Skill_id = ' 3 ' AND experience> = ' 1 '))
AND exists (select * from ApplicantSkill where Applicant_id = u.id AND (Skill_id = ' 4 '))
AND minSalaryExpectation BETWEEN ' 1000 ' AND ' 10000 ';

To work, like, works, but somehow irritates an amount correlated subqueries, I feel that it is not possible somehow with JOIN to refine, but I can think as... Looks as typical enough task. Help, please!

2

Re: Help to refine request

It seems here so it turned out

SELECT u. *, a.* FROM User u
JOIN Applicant an ON a.id = u.id
JOIN ApplicantArea aa1 ON aa1.Applicant_id = u.id AND aa1.ProfessionalArea_id = ' 10'
JOIN ApplicantArea aa2 ON aa2.Applicant_id = u.id AND aa2.ProfessionalArea_id = ' 13'
JOIN ApplicantRegion ar1 ON ar1.Applicant_id = u.id AND (ar1.Region_id = ' 1 ' OR ar1.Region_id = ' 4 ')
JOIN ApplicantSkill as1 ON as1.Applicant_id = u.id AND (as1.Skill_id = ' 1 ' AND as1.experience> = ' 1 ')
JOIN ApplicantSkill as2 ON as2.Applicant_id = u.id AND (as2.Skill_id = ' 3 ' AND as2.experience> = ' 1 ')
JOIN ApplicantSkill as3 ON as3.Applicant_id = u.id AND (as3.Skill_id = ' 4 ')
WHERE u. Role_id = ' 3'
AND minSalaryExpectation BETWEEN ' 1000 ' AND ' 10000 ';

3

Re: Help to refine request

Alexey Furmanov;
Generally requests are not equivalent by returned result....

4

Re: Help to refine request

Kind  - Eh;
In the second case it is necessary DISTINCT?