CODEWITHSUNDEEP
mysqlsql
BambiOurLord
BambiOurLord

Reputation: 372

MySQL query based on results of another query

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.

Upvotes: 0

Views: 57

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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';

Upvotes: 3

Related Questions