SQL Query select one row based on results of another row

Question

I included a screenshot of a demo entry to the table. I know how to write an SQL query to select data when all the information is on the same row, but this presents a new challenge to me. I want to select the meta_value result to a meta_key for a user_id only if pw_user_status has the value of approved. I'm not sure how to write this kind of query.

Source data:

Can you at least show us what your expected output is?

Tim Biegeleisen
– Tim Biegeleisen

2017-03-14 02:52:19 +00:00
Commented Mar 14, 2017 at 2:52 — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 14, 2017 at 2:52

Tim Biegeleisen · Accepted Answer · 2017-03-14 03:05:13Z

1

You can use a subquery which identifies users having approved status, and then select certain key/value pairs for those users:

SELECT meta_key, meta_value
FROM yourTable t1
WHERE t1.meta_key IN ('nickname', 'first_name', ...) AND
     EXISTS (SELECT 1 FROM yourTable t2
             WHERE t2.user_id = t1.user_id AND
                   t2.meta_key = 'pw_user_status' AND
                   t2.meta_value = 'approved')

Demo here:

Rextester

edited Mar 14, 2017 at 3:05

answered Mar 14, 2017 at 2:59

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query select one row based on results of another row

1 Answer 1

Rextester

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related