MySQL query based on results of another query

Question

I have a table in MySQL which looks like this.

+---------+------------+--------------+
| user_id |    key     |     value    |
+---------+------------+--------------+
|    1    | full_name  |  John Smith  |
+---------+------------+--------------+
|    1    | is_active  |      1       |
+---------+------------+--------------+
|    1    | user_level |Administrator |
+---------+------------+--------------+

I need to get value of key full_name where user_id is 1, but only if value of key is_active is 1. I can do it with 2 separate queries, but I would like to know if it is possible to do it in a single query.

Note: I cannot change the structure of the table.

Gordon Linoff · Accepted Answer · 2018-10-19 10:57:34Z

3

One method is to use joins:

select tn.value
from t tn join
     t ta
     on tn.user_id = ta.user_id and ta.key = 'active'
where tn.key = 'fullname';

answered Oct 19, 2018 at 10:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-19 11:07:22Z

1

i think you need below query by using exists

select t.value from your_table t where 
exists ( select 1 from your_table t1
         where t1.user_id=t.user_id 
         and t1.key='is_active'
       ) and t.key='full_name'

DEMO IN MYSQL 8

 value
john smith

edited Oct 19, 2018 at 11:07

answered Oct 19, 2018 at 10:56

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

MySQL query based on results of another query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related