seo Question posted in
Some basic information to get you started with this can be found here.

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

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search