开发者

SELECT doesn't return value on "AND"

Am I missing something? My table contains:

  • country with the value 'usa'
  • school-type with the value '4 Year University'

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 the开发者_如何学JAVAm, 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'))


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.


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'))


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜