Subquery returning all rows...

[Jim R]

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:

wp_users:

Output looks like this:

 

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..

 

Edited December 1, 2021 by Jim R

[kicken]

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 December 1, 2021 by kicken

[Jim R]

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. 

[Jim R]

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 December 1, 2021 by Jim R