CODEWITHSUNDEEP
mysqlsql
Norbert
Norbert

Reputation: 2771

SELECT doesn't return value on "AND"

Am I missing something? My table contains:

If I use row 2, the query returns '220'. If I use row 4, it also returns '220'. If I use both with 'OR' between them, it returns '220' twice, but if I use 'AND', it doesn't return anything. Any ideas?

SELECT post_id FROM wp_postmeta WHERE
(   (meta_key='school-type' AND meta_value='Community College')
 OR (meta_key='school-type' AND meta_value='4 Year University'))
AND
(   (meta_key='country' AND meta_value='usa')
 OR (meta_key='country' AND meta_value='canada'))

Upvotes: 1

Views: 226

Answers (3)

William
William

Reputation: 3529

The problem is, a single row can't have a meta_key of both "school-type" and "country". Combining the two different searches results in 0 results because of this.

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30141

WHERE filters each row, so it is not possible for meta_key to be school-type and country.

Try a HAVING clause:

SELECT post_id
FROM wp_postmeta
WHERE meta_key='school-type' OR meta_key='country'
GROUP BY post_id
HAVING SUM(
    IF(meta_key='school-type',
      meta_value='Community College' OR meta_value='4 Year University',
      meta_value='usa' OR meta_value='canada'
      )) = 2

Or, If you want to get both rows:

SELECT post_id
FROM wp_postmeta
WHERE
((meta_key='school-type' AND meta_value='Community College') OR (meta_key='school-type' AND meta_value='4 Year University'))
OR
((meta_key='country' AND meta_value='usa') OR (meta_key='country' AND meta_value='canada'))

Upvotes: 0

Vivek Goel
Vivek Goel

Reputation: 24160

you query is wrong . Let's take example if meta_key='school-type' than it can't be country so your second condition will always return fast.

Upvotes: 0

Related Questions