CODEWITHSUNDEEP
mysqlsqlwordpresswoocommerce
evox
evox

Reputation: 45

MySQL: Return list of values in one table conditional on another table

I need to create a view of student ID numbers that are active (paying) members.

Student numbers are stored in a table called usermeta like so:

user_id          meta_key          meta_value
---------------------------------------------
      1         studentid                1234

Whether the person is a member is stored in the posts table (Woocommerce/Wordpress setup...):

post_author         post_status
-------------------------------
          1          wcm_active

user_id and post_author are the unique IDs identifying each member/user.

I know I can return a list of ID numbers like so:

SELECT meta_value FROM usermeta WHERE meta_key = "studentid"

I just can't quite figure out how to make it conditional on if post_status = wcm-active

If anyone has some input it would be greatly appreciated.

Upvotes: 2

Views: 81

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

You are looking for IN or EXISTS:

SELECT meta_value 
FROM usermeta 
WHERE meta_key = 'studentid'
AND user_id IN (SELECT post_author FROM posts WHERE post_status = 'wcm_active')

Upvotes: 1

Related Questions