CODEWITHSUNDEEP
mysqlsql
justaguy
justaguy

Reputation: 187

mysql select value of column based on another select

I have two tables:

TABLE 1:
|| route_id || years ||
===========================
|| 1        || 10    ||

|| 2        || 15    ||

TABLE 2:

|| route_id || years_1 || years_5 || years_10 || years_15 ||

|| 1        || 0.5     || 0.6     || 0.7     || 0.8     ||
|| 2        || 0.3     || 0.35     || 0.4     || 0.45     ||

I want to select a value from table 2 , based on the value in table 1. for example:
select years_+(select years from table 1 where route_id = 1) from table 2 where route_id = 1

Is it possible ?

For extra credit :) :

I dont want to use case as there are a lot of columns...

The whole statement is a view.

This statement is under an COALESCE so if there isn't any column with this value, select another column.

Thanks for your help!!!!

This is my statment now:

select 
        `a`.`user_id` AS `userId`,
        `a`.`answer_type` AS `answerType`,
        `a`.`route_number` AS `routeNumer`,
        `a`.`route_amount` AS `routeAmount`,
        `a`.`route_years` AS `routeYears`,
        `a`.`route_intrest` AS `routeIntrest`,
        `a`.`route_linkage` AS `routeLinkage`,
COALESCE('r2.intrest_year_XXXXXX'),r.route_intrest) as routeRate
    from
        (`algoanswer` `a`
        left join `route` `r` ON ((`a`.`route_number` = `r`.`route_id`))
        left join `route_intrest` `r2` ON ((`a`.`route_number` = `r2`.`route_id`)))
    where
        (`a`.`answer_id` = (select 
                max(`algoanswer`.`answer_id`)
            from
                `algoanswer`
            where
                (`a`.`user_id` = `algoanswer`.`user_id`)))

i need to insert the right years instead of the xxx in: COALESCE('r2.intrest_year_XXXXXX'),r.route_intrest) as routeRate

Upvotes: 0

Views: 3623

Answers (3)

Radhamani Muthusamy
Radhamani Muthusamy

Reputation: 328

Try this

declare @sql nvarchar(100),
@columnname nvarchar(50);

set @sql=(select 'years_'+ cast((t1.years) as nvarchar(10)) from table1 t1 where t1.route_id=1);
set @columnname=@sql;
select @columnname from table2 ;

Upvotes: 0

aebersold
aebersold

Reputation: 11506

I'm not exactly sure what you want. I assumed you want to sum all years from table1 according to each route_id?

SELECT 
    t2.*, 
    SELECT 
        SUM(years) 
        FROM table1 t1 
        GROUP BY t1.route_id 
        WHERE t1.route_id = t2.route_id
    AS years_
FROM 
    table2 t2
WHERE 
    t2.route_id = 1

Upvotes: 0

A Paul
A Paul

Reputation: 8251

I think this will work for you

SET @config := (select years from table1 where route_id = 1);
SET @sql := CONCAT('SELECT years_', @config, ' FROM table2 where route_id = 1');

PREPARE stmt FROM @sql;
EXECUTE stmt;

Upvotes: 3

Related Questions