using result of main query as column name in sub query

user_15105327 0
Viewed 204

I am trying to use result cell of main query in subquery.

I have two tables

first table is telephone

id name month
151 raj Jan_2021
152 danny Feb_2021

second table is ss

id Jan_2021 Feb_2021
151 2500 2200
152 1000 500

Mysql query iam using

SELECT 
name,
id,
month,
(   select SOD.month 
    from ss 
    where Empid=SOD.eid
) as ava 
FROM 
telephone as SOD 
join ss 
on SOD.id=ss.Emp_id

but i am not getting the exact result what i want . please help me to get this result from this tables

id name month ava
151 raj Jan_2021 2500
152 danny Feb_2021 500
mysql sql
2 Answers

Please check the answer in the fiddle : http://sqlfiddle.com/#!9/7c62c/7

select tel.id,tel.name,tel.month,
case
when tel.month='jan_2021' then ss.jan_2021
when tel.month='feb_2021' then ss.feb_2021
when tel.month='mar_2021' then ss.mar_2021
end
as monthly
from telephone tel,ss 
where tel.id=ss.id
user_141910540

I would approach this with JOIN and conditional logic:

select t.*
       (case t.month when 'jan_2021' then ss.jan_2021
             t.month when 'feb_2021' then ss.feb_2021
             t.month when 'mar_2021' then ss.mar_2021
        end) as monthly
from telephone t join
     ss 
     on t.id = ss.id
user_11440350
Related