MySQL Query Returning Empty

Question

I have 2 tables - posts and postmeta.

posts
ID   title   category  post_status  post_type    

1    ABC      cat-1    Publish      Store
2    DEF      cat-2    Publish      Store
3    GHI      cat-3    Publish      Store
4    JKL      cat-2    Publish      Store
5    XYZ      cat-5    Draft        Store
6    MNO      cat-9    Publish      Article

and

postmeta
meta_id post_id  meta_key    meta_value

109       1       city          1
110       1       featured      h
111       2       city          1,2
112       2       featured      both
113       3       city          2,3
114       3       featured      both
115       4       city          1
116       4       featured      n
117       5       city          1,4
118       5       featured      h 
119       6       city          1
120       6       featured      h

I am trying to run a query that would give me a list of posts which has the following conditions:

Whose value against city has 1 AND
whose value for featured is either h or both AND
whose post status is Publish AND
whose post type is Store
Order them by title

The query I am trying is

SELECT DISTINCT posts.ID , posts.*, postmeta.*
            FROM posts, postmeta
            WHERE posts.ID = postmeta.post_id
            AND (postmeta.meta_value  = 'h' OR postmeta.meta_value = 'both') 

AND (postmeta.meta_key = 'post_city_id' AND (postmeta.meta_value LIKE '%,1,%' OR postmeta.meta_value LIKE '%1,%'  OR postmeta.meta_value LIKE '%,1%' OR postmeta.meta_value LIKE '%1%'))

            AND posts.post_status = 'Publish' 
            AND posts.post_type = 'Store'

            ORDER BY (SELECT postmeta.meta_value from postmeta where (posts.ID = postmeta.post_id) and postmeta.meta_key LIKE '%home_featured_type%') asc, posts.post_title LIMIT 0,6

The correct returns would be IDs 1 and 2 i.e. abc and def. But I am getting empty result. I cannot figure out where it is falling apart. How can this be fixed?

order by posts.title I have mistaken mentioned posts.post_title here in the query...it would be posts.title — Ranadeep
– Ranadeep, Commented Jun 19, 2012 at 16:18

biziclop · Accepted Answer · 2012-06-19 17:55:33Z

here is a fixed query, but I still don't understand the mysterious ORDER BY (SELECT) thingie.

http://sqlfiddle.com/#!2/5ce5a/19

SELECT DISTINCT posts.ID , posts.*, postmeta_city.*, postmeta_featured.*

FROM       posts

INNER JOIN postmeta AS postmeta_city
     ON     postmeta_city.post_id = posts.ID
    AND     postmeta_city.meta_key = 'city'
    AND (   postmeta_city.meta_value LIKE '%,1,%'
         OR postmeta_city.meta_value LIKE '%1,%'
         OR postmeta_city.meta_value LIKE '%,1%'
         OR postmeta_city.meta_value LIKE '%1%'
        )
INNER JOIN postmeta AS postmeta_featured
     ON     postmeta_featured.post_id = posts.ID
    AND     postmeta_featured.meta_key = 'featured'
    AND (   postmeta_featured.meta_value = 'h'
         OR postmeta_featured.meta_value = 'both'
        )

WHERE posts.post_status = 'Publish' 
  AND posts.post_type = 'Store'

ORDER BY (
    SELECT postmeta.meta_value
    FROM postmeta
    WHERE ( posts.ID = postmeta.post_id )
      AND postmeta.meta_key LIKE '%home_featured_type%'
  ) asc,
  posts.title

LIMIT 0,6;
;

Updated with other people's ideas, please upvote them:

http://sqlfiddle.com/#!2/5ce5a/33

SELECT DISTINCT posts.ID , posts.*, postmeta_city.*, postmeta_featured.*

FROM       posts

INNER JOIN postmeta AS postmeta_city
     ON postmeta_city.post_id = posts.ID
    AND postmeta_city.meta_key = 'city'
    AND FIND_IN_SET('1', postmeta_city.meta_value)

INNER JOIN postmeta AS postmeta_featured
     ON postmeta_featured.post_id = posts.ID
    AND postmeta_featured.meta_key = 'featured'
    AND postmeta_featured.meta_value IN ('h','both')

WHERE posts.post_status = 'Publish' 
  AND posts.post_type = 'Store'

ORDER BY (
    SELECT postmeta.meta_value
    FROM postmeta
    WHERE ( posts.ID = postmeta.post_id )
      AND postmeta.meta_key LIKE '%home_featured_type%'
  ) asc,
  posts.title

LIMIT 0,6;
;

Wonderful. I am using the updated solution. Works perfectly. Thanks a ton.

Zane Bien · Accepted Answer · 2012-06-19 18:02:14Z

1

You're getting an empty result set because you are ANDing the meta_value column so it has to equal two values at the same time which is impossible. Something like val = '1' AND val = 'both' will always return false and none of the rows would join. Instead, you must use an OR between conditions: city -> 1 and featured -> h/both.

Since the post must contain both city -> 1 and featured -> h/both (which are not across columns but across multiple rows), you'll need a HAVING clause in conjunction with a GROUP BY to ensure each post joins with two rows, satisfying both conditions... not one or the other.

Also, that is an awful lot of LIKE's to check for the existance of a 1. You can use FIND_IN_SET instead:

SELECT
    *
FROM
    posts a
INNER JOIN
    postmeta b ON a.ID = b.post_id
    AND
    (
        (b.meta_key = 'city' AND FIND_IN_SET('1', b.meta_value) > 0)
        OR  
        (b.meta_key = 'featured' AND b.meta_value IN ('h', 'both'))
    )
WHERE
    a.post_status = 'Publish'
    AND a.post_type = 'Store'
GROUP BY
    a.ID
HAVING
    COUNT(*) = 2
ORDER BY
    a.title

edited Jun 19, 2012 at 18:02

answered Jun 19, 2012 at 17:30

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

1 Comment

Ranadeep Over a year ago

While I have accepted biziclop's updated solution, I thank you for the explanation. I will keep this in mind for the future. Thanks a ton.

Nishu Tayal · Accepted Answer · 2012-06-19 16:37:33Z

0

Try this :

SELECT DISTINCT posts.ID , posts.*, postmeta.*
            FROM posts AS p INNER JOIN postmeta AS pm
            WHERE p.ID = pm.post_id
            AND (pm.meta_value  in ('h','both') 

AND (pm.meta_key = 'city' AND 
(pm.meta_value LIKE '%,1,%' OR pm.meta_value LIKE '%1,%'  OR pm.meta_value LIKE '%,1%' OR pm.meta_value LIKE '%1%'))

            AND posts.post_status = 'Publish' 
            AND posts.post_type = 'Store'

            ORDER BY p.title LIMIT 0,6

Since you want order by title only, so there is no need to write any query in 'order by' clause.

answered Jun 19, 2012 at 16:37

Nishu Tayal

20.9k8 gold badges53 silver badges105 bronze badges

Collectives™ on Stack Overflow

MySQL Query Returning Empty

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related