MySQL - Conditional select from multiple column

Question

I want to check the value of two columns and create a third one based on those values. Here's my view deff:

CREATE VIEW `vw_movies` AS
    select 
        `tbl_movies`.`id` AS `id`,
        `tbl_movies`.`vendor_id` AS `vendor_id`,
        `tbl_vendors`.`title` AS `title_vendor`,
        `tbl_movies`.`title` AS `title`,
        `tbl_movies`.`year` AS `year`,
        `tbl_movies`.`synopsis` AS `synopsis`,
        `tbl_movies`.`plot` AS `plot`,
        `tbl_movies`.`director` AS `director`,
        `tbl_movies`.`stars` AS `stars`,
        `tbl_movies`.`language` AS `language`,
        `tbl_movies`.`rating_id` AS `rating_id`,
        `tbl_ratings`.`title` AS `rating`,
        `tbl_movies`.`img_path` AS `img_path`,
        `tbl_movies`.`trailer_path` AS `trailer_path`,
        `tbl_movies`.`file_path` AS `file_path`,
        `tbl_movies`.`is_active` AS `is_active`,
        (case `tbl_movies`.`is_active`
            when 0 then 'No'
            when 1 then 'Yes'
        end) AS `is_active_text`,
        `tbl_movies`.`is_confirmed` AS `is_confirmed`,
        (case `tbl_movies`.`is_confirmed`
            when 0 then 'No'
            when 1 then 'Yes'
        end) AS `is_confirmed_text`,
        `tbl_movies`.`created` AS `created`,
        `tbl_movies`.`modified` AS `modified`
    from
        ((`tbl_movies`
        join `tbl_vendors` ON ((`tbl_movies`.`vendor_id` = `tbl_vendors`.`id`)))
        join `tbl_ratings` ON ((`tbl_movies`.`rating_id` = `tbl_ratings`.`id`)))

What I want to check is the value of is_confirmed and is_confirmed:

if is_confirmed == 1 && is_confirmed == 1 then "Live"
else if is_confirmed == 0 && is_confirmed == 0 then "Initial"
else if is_confirmed == 1 && is_confirmed == 0 then "In-active/Dead Link"

How can I implement such if-select statement in MySQL?

Your sample view has several case statements. That is how you would implement such logic. Don't you understand how the code you posted actually works? — Gordon Linoff
– Gordon Linoff, Commented Feb 24, 2014 at 22:04

Lucia Pasarin · Accepted Answer · 2014-02-24 22:10:15Z

1

You can use CASE for that like this:

SELECT CASE WHEN is_confirmed = 1 AND is_confirmed = 1 THEN "Live"
            WHEN is_confirmed = 0 AND is_confirmed = 0 THEN "Initial"
            WHEN is_confirmed = 1 AND is_confirmed = 0 THEN 
"In-active/Dead Link" 
END;

Check the reference here:

https://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case

I used the names you gave to these columns. However, I think you should renamed them, since currently the behavior would be:

is_confirmed = 1 AND is_confirmed = 1 -> might be true

is_confirmed = 0 AND is_confirmed = 0 -> might be true

is_confirmed = 1 AND is_confirmed = 0 -> never true (it is the same column)

edited Feb 24, 2014 at 22:10

answered Feb 24, 2014 at 22:02

Lucia Pasarin

2,3181 gold badge22 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

MySQL - Conditional select from multiple column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related