CODEWITHSUNDEEP
mysqlview
trap
trap

Reputation: 2640

Mysql View new field value

how can I create a new field depending on another field in the same view?

For example: I have table car, with the fields: car_name and car_type.

Depending which car_type it has (A, B, C or D) i wanted to create a new field in the view "price_class"

so if car_type = A then price_class is > 100.000. If it is B then > 150.000 for C and D I want to set the same value

CREATE VIEW `car_price` AS
SELECT 
    `car`.`car_name` AS `name`,
    `car`.`car_type` AS `car_type`
FROM
    `car`

Thank you in advance!

Upvotes: 0

Views: 18

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Create another field based on condition using case when

CREATE VIEW `car_price` AS
SELECT 

    `car`.`car_name` AS `name`,
    `car`.`car_type` AS `car_type`,
case when `car`.`car_type`='A' then '> 100.000'
when `car`.`car_type`='B' then '> 150.000'
when `car`.`car_type` in ('C','D') then '> 300.000' end as price_class 
FROM
    `car`

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

You may use a CASE expression:

CREATE VIEW car_price AS
SELECT 
    car_name AS name,
    car_type AS car_type,
    CASE WHEN car_type = 'A' THEN '> 100.000'
         WHEN car_type = 'B' THEN '> 150.000'
         WHEN car_type IN ('C', 'D') THEN '> 300.000' END price_class
FROM car;

Upvotes: 2

Related Questions