SQL WordPress Custom Query Inner Join

1 Views

I’m having some difficulty with my SQL statement. I’m doing a query on WordPress to display posts based on multiple post meta fields. When I do the query and filter with only one meta field, or a OR on multiple it works, however AND on multiple fails. 

SELECT wposts . *
FROM wp_posts wposts
INNER JOIN (
  SELECT post_id
  FROM wp_postmeta wpostmeta
  WHERE (
    (wpostmeta.meta_key = 'ulnooweg_business_industry'
      AND wpostmeta.meta_value = 'Legal Services')
    AND (
    wpostmeta.meta_key = 'ulnooweg_business_province'
      AND wpostmeta.meta_value = 'New Brunswick')
  )
  GROUP BY post_id
) 
AS t ON t.post_id = wposts.ID
WHERE wposts.post_status = 'publish'
AND wposts.post_type = 'business'
ORDER BY wposts.post_title ASC
LIMIT 0 , 30
Post Views: 1

Related posts

Leave a Reply

3 comments

  1. Your query is testing if meta_key (and meta_value) is 2 different values in the same row, which is impossible. But I see what you are trying to do..

    Try joining the wp_postmeta table twice except each with an ON clause that excludes all rows except those that satisfy the meta_key condition:

    SELECT 
    p.*, 
    GROUP_CONCAT(CONCAT(pm.meta_key,':',pm.meta_value) SEPARATOR ',') AS meta_values
FROM 
    wp_posts p
    JOIN wp_postmeta pm ON pm.post_id = p.ID
    JOIN wp_postmeta pm_bi ON (pm_bi.post_id = p.ID AND pm_bi.meta_key = 'ulnooweg_business_industry')
    JOIN wp_postmeta pm_bp ON (pm_bp.post_id = p.ID AND pm_bp.meta_key = 'ulnooweg_business_province')
WHERE 
    pm_bi.meta_value = 'Legal Services'
    AND pm_bp.meta_value = 'New Brunswick'
    AND p.post_type = 'business'
    AND p.post_status = 'publish'
GROUP BY p.ID
ORDER BY p.post_title ASC

    Note: I joined the wp_postmeta table 3 times here to help prove that the conditions are satisfied, but you can remove the GROUP_CONCAT line (and the comma on the previous line of course) and the first JOIN to wp_postmeta and the query will work the same.

  2. In the subquery, it looks like it’s looking for records where both wpostmeta.meta_key = 'ulnooweg_business_industry' and wpostmeta.meta_key = 'ulnooweg_business_province' — in other words, wpostmeta.meta_key needs to be equal to two strings simultaneously to satisfy this condition. Also, it’s looking for wpostmeta.meta_value = 'Legal Services' and wpostmeta.meta_value = 'New Brunswick'.

    My guess is that this is what you want in the WHERE clause of the subquery — change one of the ANDs to an OR:

    ....
WHERE (
  (wpostmeta.meta_key = 'ulnooweg_business_industry'
    AND wpostmeta.meta_value = 'Legal Services')
  OR ( -- changed to an OR
  wpostmeta.meta_key = 'ulnooweg_business_province'
    AND wpostmeta.meta_value = 'New Brunswick')
)
....

  3. The problem is in the where clause of your inner select; I’m guessing wpostmeta returns MULTIPLE rows. A previous comment that a string can’t be two values is correct. The 2nd approach should work if the 1st doesn’t

    at first I thought

    WHERE
  ((wpostmeta.meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services') OR 
   (wpostmeta.meta_key = 'ulnooweg_business_province' AND wpostmeta.meta_value = 'New Brunswick'))
Group by Post_ID
HAVING count(post_ID) = 2

    This will work ONLY if there is only one record in the wpostmeta for each type of entry. If
    postmeta.meta_key = ‘ulnooweg_business_industry’ AND wpostmeta.meta_value = ‘Legal Services’ can occur twice, then the above does’t work.

    2nd approach

    Select wposts.*
FROM WP_Posts wposts
INNER JOIN  (
Select POST_ID from WP_POSTMeta where meta_key = 'ulnooweg_business_industry' AND wpostmeta.meta_value = 'Legal Services'
INTERSECT
SELECT POST_ID FROM WP_POST_META WHERE meta_key = 'ulnooweg_business_province' AND wpostmeta.meta_value = 'New Brunswick'
) 
AS T on T.Post_ID = wposts.ID