Search for multiple values within MySQL issue

Question

I am trying to search for two keywords within the data table, but am running into issues where the following returns no resolutes even through I know there are about 36.

Can somebody help me write a better query?

SELECT user_id, name, value
FROM data
    INNER JOIN fields ON data.field_id = fields.id 
WHERE name IN ('Type', 'Category')
AND value = 'Keyword 1'
AND value = 'Keyword 2'  /* If I use OR it returns 50 records of those +14 are irrelevant*/
GROUP BY user_id
HAVING COUNT(user_id)>1
ORDER BY value

Am I missing something?

Larry Lustig · Accepted Answer · 2012-04-18 03:33:25Z

3

Note the addition of the parentheses:

SELECT user_id, name, value
FROM data
INNER JOIN fields ON data.field_id = fields.id 
WHERE name IN ('Type', 'Category')
AND (value = 'Keyword 1'
OR value = 'Keyword 2') 
GROUP BY user_id
HAVING COUNT(user_id)>1
ORDER BY value

SQL binds from left to right, so

WHERE name IN ('Type', 'Category') AND value = 'Keyword 1' OR value = 'Keyword 2'

is equivalent to

WHERE (name IN ('Type', 'Category') AND value = 'Keyword 1') OR value = 'Keyword 2'

so you can see where the extra records came from — all records with value = 'Keyword 2' will be included irrespective of name.

Of course, you could also write:

WHERE name IN ('Type', 'Category') AND value IN ('Keyword 1', 'Keyword 2')

which eliminates the precedence issue.

answered Apr 18, 2012 at 3:33

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thisisnousername Over a year ago

Thanks Larry have just tested option 4 and is working well. Except that I am now realising that if lets say a user has two records of 'Keyword 1' but not 'Keyword 2' it still gets added to the query.<br/><br/>Is there anyway to make the query only return records that have 'Keyword 1' and 'Keyword 2' from both name fields?

Larry Lustig Over a year ago

Most likely yes, but you'll have to state more clearly what records you want returned. My suggestion is to post a new question and include some sample input records and show what results you expect from that input.

The Scrum Meister · Accepted Answer · 2012-04-18 03:32:45Z

0

You need to group the conditions like this:

WHERE name IN ('Type', 'Category')
AND (value = 'Keyword 1'
    OR value = 'Keyword 2' )

Otherwise MySql will return rows that have Keyword 2 but their name is not Type or Category.

Alternatively, look into Full-Text Search.

answered Apr 18, 2012 at 3:32

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

2 Comments

thisisnousername Over a year ago

Yeah Full-Text Search is a little over my head :) Thanks for the link

Ankit Sharma Over a year ago

@thisisnousername check my variation for this answer

Ankit Sharma · Accepted Answer · 2012-04-18 07:09:19Z

0

Try this-

SELECT user_id, name, value
FROM data
    INNER JOIN fields ON data.field_id = fields.id 
WHERE name IN ('Type', 'Category')
AND value in( 'Keyword 1','Keyword 2') 
GROUP BY user_id
HAVING COUNT(user_id)>1
ORDER BY value

answered Apr 18, 2012 at 7:09

Ankit Sharma

4,0792 gold badges31 silver badges50 bronze badges

Collectives™ on Stack Overflow

Search for multiple values within MySQL issue

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related