SELECT users with multiple AND / OR / IN conditions in 1 column

Question

For simplicity let's say the product is a job board - the person using the job board can filter the jobs by multiple filters e.g. Location, Function, Skills, Keywords etc.

I am trying to create a query that selects unique Jobs from a table that match a users filtering criteria.

The trouble seems to be combining multiple OR operators with multiple AND operators. I think maybe the table is set up incorrectly.

Using the table below as a very simple example, I want to see Jobs that have

Location = London OR Manchester
AND
Function = Marketing OR Sales
AND 
Keyword  = SEO 

Note: Location, Function and Keyword are not Groups, they are the 
filter of the user searching the jobs (maybe that is the problem?)

JobId   | Group                   | Value
1       | LocationJob             | London
1       | LocationCovered         | Leeds
1       | FunctionJob             | Tech
1       | FunctionJob2            | Engineering
1       | SkillRequired           | PHP
1       | SkillOptional           | Python
2       | LocationJob             | Newcastle
2       | LocationCovered         | Manchester
2       | FunctionJob             | SEO
2       | FunctionJob2            | PPC
2       | SkillRequired           | Marketing
3       | LocationJob             | Manchester
3       | LocationCovered         | Leeds
3       | FunctionJob             | Sales
3       | FunctionJob2            | Business Development
3       | SkillRequired           | SEO

Result should be Jobs 2 and 3

I am currently doing the below, but I have hundreds of thousands of records so it can be very slow if lots of joins..

SELECT TKeywords.UserID
FROM `Search` TKeywords
  JOIN `Search` TFunctions ON TKeywords.UserID = TFunctions.UserID AND 
       TFunctions.Value IN ('Function1', 'Function2','Function3')
  JOIN `Search` TSkills ON TKeywords.UserID = TSkills.UserID AND 
       TSkills.Value IN ('Skills1', 'Skills2','Skills3')
  JOIN `Search` TLocation ON TKeywords.UserID = TLocation.UserID AND 
       TLocation.Value IN ('Location1', 'Location2','Location3')
WHERE TKeywords.Value IN ('Keyword1', 'Keyword2', 'Keyword3')

Any help much appreciated. Thanks

Possible duplicate of [Sql select where clause issue](https://stackoverflow.com/questions/15988755/sql-select-where-clause-issue) — MackM, Feb 22 '18 at 16:21

score 1 · Answer 1 · answered Feb 22 '18 at 16:21

1

This should work:

select s1.UserId from search s1
join (
    select UserId from search 
    where Group = 'Industry' and Value = 'Construction'
) s2 on s1.UserId = s2.UserId
where Group = 'Skill' and Value in('PHP', 'Python')

answered Feb 22 '18 at 16:21

isaace

3,336
1
9
22

+1, this is probably the optimal solution. There is no harm to use "using(UserId)" instead of "s1.UserId = s2.UserId" in this precise case too, but that's just syntax – Matthieu.V Feb 22 '18 at 16:48
Hi I've updated the question to give a little more clarity, does this solution still apply? – George Vincent Feb 22 '18 at 20:03
what is your desired output for the new sample data? Please add it to the question. – isaace Feb 22 '18 at 20:09
In your updated question you have many rows of 1 then rows of 2 and then more rows with 1. Is this a mistake (should it be 3) ? – isaace Feb 22 '18 at 20:33
yes should be 3, i'll update sorry, results should be jobs 2 and 3 – George Vincent Feb 22 '18 at 22:28
"Location = London OR Manchester" which location? Job or covered? which function job or job2? – isaace Feb 22 '18 at 22:32
also you wrote "Function = Marketing OR Sales" but in your data, Sales is related to skills not to a function. – isaace Feb 22 '18 at 22:35
Yes that is the problem exactly. The value e.g. sales can belong to multiple groups. In terms of results it does not matter which group the value was found in, just whether it is found or not. For the purposes of the query it is best to ignore the groups. – George Vincent Feb 23 '18 at 08:23

score 0 · Answer 2 · answered Feb 22 '18 at 16:20

One off the options would be to use group by and sum to validate.

SELECT 
 Userid
FROM 
 [table]
GROUP BY 
 Userid
HAVING 
(
     SUM(`Group` = 'Skill') AND SUM(Value = 'PHP')
   OR
     SUM(`Group` = 'Skill') AND SUM(Value = 'Python')
) 
 AND
(
  SUM(`Group`) = 'Industry' AND SUM(Value = 'Construction')
)

demo http://www.sqlfiddle.com/#!9/a6034/2

SELECT users with multiple AND / OR / IN conditions in 1 column

2 Answers2