Jump to content

Subquery returning all rows...


Jim R
 Share

Recommended Posts

If you're familiar with WordPress, each user_id produces about 15-20 rows of usermeta data.  I just want to get a couple of them, but the two rows for a User have  serialized data.  

 

I'm looking to producing a list of Users who subscribe to a certain level -- sub_level -- and which part of Indiana they're from -- sub_region: North(1), Central(2) or South(3).  Truly just interested in the s2member_ value in sub_level and the # (1,2 or 3) in sub_region, so there is likely a better way to get just that than LIKE, but I can code it how I want in the PHP output.

I'm wanting only the rows of Users where sub_level isn't NULL.

 

select 
u.id,
u.user_login,
u.user_email,
u.user_registered,

(select um1.meta_value from wp_usermeta um1 
	where u.id = um1.user_id and um1.meta_value LIKE concat ('%', 's2member_', '%') and um1.meta_key = 'wp_capabilities') as sub_level,

(select meta_value from wp_usermeta um2
	where u.id = um2.user_id and um2.meta_value LIKE concat ('%', 'county', '%')) as sub_region	

from wp_users u

 

Table structures:

wp_usermeta:

image.png.00201ec0293de7acf9abc287398f3eae.png

wp_users:

image.png.cb2857573f798d8ab231a7f28d5a3ceb.png

Output looks like this:

image.png.d9b69aa5a32ebcbbdf0fd0829f1d1fed.png

 

I tried 'WHERE sub_level is not null', but as you know, can't use an alias in a WHERE.

I tried 'WHERE um1.meta_value is not null' in the subquery, but that didn't change the results.  I also tried it in the outer query, but I got an unknown column error..

 

image.png

Edited by Jim R
Link to comment
Share on other sites

use a join rather than sub queries in the select clause.

select 
	u.id,
	u.user_login,
	u.user_email,
	u.user_registered,
	sub_level.meta_value as sub_level,
	sub_region.meta_value as sub_region
from wp_users u
inner join wp_usermeta sub_level on u.id = sub_level.user_id and sub_level.meta_key = 'wp_capabilities' and sub_level.meta_value like '%s2member_%'
inner join wp_usermeta sub_region on u.id = sub_region.user_id and sub_region.meta_value like '%county%'

Does the region not have an appropriate meta_key value to filter on? 

Edited by kicken
  • Like 1
Link to comment
Share on other sites

18 minutes ago, kicken said:

use a join rather than sub queries in the select clause.

select 
	u.id,
	u.user_login,
	u.user_email,
	u.user_registered,
	sub_level.meta_value as sub_level,
	sub_region.meta_value as sub_region
from wp_users u
inner join wp_usermeta sub_level on u.id = sub_level.user_id and sub_level.meta_key = 'wp_capabilities' and sub_level.meta_value like '%s2member_%'
inner join wp_usermeta sub_region on u.id = sub_region.user_id and sub_region.meta_value like '%county%'

Does the region not have an appropriate meta_key value to filter on? 

The use of meta_key for the sub level is because there are a couple of other rows with s2member entries for each User.  There is just one row for each User with the county data.  The county data is actually a custom entry I put in the User registration form.

 

I did start using JOIN first, but I didn't remember (or think of) using aliases.  I'll give this a try...thank you. 

Link to comment
Share on other sites

Oh...one thing...

Not everyone will have a County entry.  There is a level of registration that is for college coaches.  I didn't think about that, until I saw the results.  Otherwise it cleaned up my NULL entries.

 

EDIT:  So I made the sub_region a LEFT JOIN, and it worked. 

Edited by Jim R
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.