Cannot query multiple values on an Inner Join

3 Views

I’m using this sql to return results based on an inner join with 3 meta values. It only seems to work with 1 AND ( ), when i add the other two it returns 0 results. 

SELECT * FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE wp_posts.post_type = 'plot'
AND wp_posts.post_status = 'publish'
AND ( wp_postmeta.meta_key = 'plot_type' AND wp_postmeta.meta_value = 'Cottage' )
AND ( wp_postmeta.meta_key = 'number_of_bedrooms' AND wp_postmeta.meta_value = '2' )
AND ( wp_postmeta.meta_key = 'property' AND wp_postmeta.meta_value = '446' )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC;
Post Views: 3

Related posts

Leave a Reply

4 comments

  1. I think you meant to use OR with the other 2 (see below). The same field can’t be 2 different things, which is why you get 0 results.

    SELECT *
  FROM wp_posts
 INNER JOIN wp_postmeta
    ON (wp_posts.ID = wp_postmeta.post_id)
 WHERE wp_posts.post_type = 'plot'
   AND wp_posts.post_status = 'publish'
   AND ((wp_postmeta.meta_key = 'plot_type' AND
       wp_postmeta.meta_value = 'Cottage') OR
       (wp_postmeta.meta_key = 'number_of_bedrooms' AND
       wp_postmeta.meta_value = '2') OR (wp_postmeta.meta_key = 'property' AND
       wp_postmeta.meta_value = '446'))
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_title ASC;

    edit, try below instead:

    select * from wp_posts
join wp_postmeta on wp_posts.ID = wp_postmeta.post_id
where wp_posts.post_type = 'plot'
  and wp_posts.post_status = 'publish'
  and concat(wp_postmeta.meta_key,'|',wp_postmeta.meta_value)
        in ('plot_type|Cottage',
           'number_of_bedrooms|2',
           'property|446');

  2. You need to join the wp_postmeta table once for each type of value you need.

    SELECT whatever, whatever
  FROM wp_posts AS p

  JOIN wp_postmeta AS plottype 
    ON (p.ID = plottype.post_id AND plottype.meta_key = 'plot_type')

  JOIN wp_postmeta AS bedrooms
    ON (p.ID = bedrooms.post_id AND bedrooms.meta_key = 'number_of_bedrooms')

  JOIN wp_postmeta AS property
    ON (p.ID = property.post_id AND property.meta_key = 'property')

 WHERE wp_posts.post_type = 'plot'
   AND wp_posts.post_status = 'publish'

   AND plottype.meta_value = 'Cottage' 
   AND bedrooms.meta_value = '2'
   AND property.meta_value = '466'

 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_title ASC;

    This wp_postmeta key/value storage is a little tricky to join to; your join criteria need to pull the appropriate key as well as the matching post ID.

    It’s well known that SELECT * is a bad idea in software. It’s especially bad when you’re joining so many tables. List the columns you need in your result set.

    Notice also that you’re using INNER JOIN with which JOIN is synonymous. If any of the values you’re pulling from the metadata are missing, so will be the row from your result set. You may or may not be better off using LEFT JOINs (You didn’t explain the purpose of the query.)

  3. It seems as some of the structure for your conditions should be changed.

    Try the following:

    SELECT * FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE wp_posts.post_type = 'plot'
AND wp_posts.post_status = 'publish'
AND (
        (wp_postmeta.meta_key = 'plot_type' AND wp_postmeta.meta_value = 'Cottage')
        OR
        (wp_postmeta.meta_key = 'number_of_bedrooms' AND wp_postmeta.meta_value = '2')
        OR 
        (wp_postmeta.meta_key = 'property' AND wp_postmeta.meta_value = '446')
    )
ORDER BY wp_posts.post_title ASC;

  4. I managed to fix the issue using WP_Meta_Query, the SQL it produced was…

    SELECT wp_posts.* FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 

WHERE 1=1

AND wp_posts.post_type = 'plot'
AND (wp_posts.post_status = 'publish')
AND ( 
(wp_postmeta.meta_key = 'property' AND CAST(wp_postmeta.meta_value AS CHAR) = '180') 
  AND (mt1.meta_key = 'plot_type' AND CAST(mt1.meta_value AS CHAR) = 'Cottage') 
  AND (mt2.meta_key = 'number_of_bedrooms' AND CAST(mt2.meta_value AS CHAR) = '2')
) 
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC;

    Thanks to everyone for the help 🙂