0

For a Wordpress project, I'd like to merge multiple queries into one using subqueries . I'm using MySQL 5.5.42. The first case would be querying a list of member_ids from the table wp_categories, then using the resulting list to query for matching ids in a post_id column from the table wp_postmeta.

What I have is this so far 

SELECT post_id 
FROM wp_postmeta 
WHERE meta_value IN ( 
    SELECT member_ids 
    FROM wp_categories 
    WHERE id=140 ) 
AND meta_key='bizid'

This seems to return empty-handed, though when I replace the subquery with a valid value I get valid results.

EDIT: This answer had the solution (https://stackoverflow.com/a/22375303/1817379) leaving me with the query:

SELECT post_id, meta_key
FROM wp_postmeta, wp_categories
WHERE wp_categories.id = 140 
    AND FIND_IN_SET(wp_postmeta.meta_value, wp_categories.member_ids)
    AND wp_postmeta.meta_key ='bizid'

Also, thanks @sa289 for the clue: it turns out the subquery returns a string rather than a list, something the IN clause won't handle.

Community
  • 1
  • 1
lintuxvi
  • 127
  • 1
  • 9

1 Answers1

1

Try running the subquery by itself and see what gets returned.

It's possible the subquery returns no results or that the results that do get returned don't have a meta_key of 'bizid' in the wp_postmeta table for the member_ids that get returned by the subquery.

EDIT:

Or if the member_ids column in the wp_categories table has more than one member_id for any given row, then it won't work. If that's the case, change it so there's one member_id per row.

sa289
  • 700
  • 3
  • 12
  • Hmm, the subquery works, and when I manually insert the values I get a valid number of results. – lintuxvi Jan 14 '16 at 20:59
  • 1
    @lintuxvi I've updated my answer to include a new possibility – sa289 Jan 14 '16 at 21:06
  • member_ids *does* have multiple values in a list. Thanks for the tip! After some searching, I ran into this salient answer: http://stackoverflow.com/a/22375303/1817379. I'm left wondering why the list becomes a problem when the subquery is used with the `IN` clause. – lintuxvi Jan 15 '16 at 01:20
  • 1
    @lintuxvi When you paste them in, the commas cause each number to be treated separately, but when it's a subquery, they are treated all as one. So if one row had 1,2,3 and you pasted that in, it'd be treated as 1, 2, or 3, but if you use the subquery, it's treated as "1,2,3" which wouldn't match anything because all the values you're trying to match with are just individual numbers. – sa289 Jan 15 '16 at 16:12
  • @lintuxvi Did this answer solve your question - if so, I'd appreciate if you could mark it as accepted so I can get credit and also that way future visitors will know it was solved. Thanks – sa289 Jan 18 '16 at 17:11
  • It was a clue, but this answer was what solved it http://stackoverflow.com/a/22375303/1817379 – lintuxvi Jan 18 '16 at 17:25