MySQL VIEW with IF statement

if-statement, mysql, view

Solution

Try below:

 CREATE VIEW news_view  
   as (SELECT news_id, news_titel, news_file_id, 
       IF(news_file_id > 0, CONCAT(file_path, file_name), 0)
           AS news_file_path
       FROM news a LEFT JOIN files b
            ON a.news_file_id= b.file_id
         JOIN categories cat
            ON a.news_category_id = cat.category_id
         JOIN users u
            ON a.news_author_id = u.user_id
      );

Add the `where` condition as required.

Problem

i have two tables in mMySQL: ``` - news (news_id, news_titel, news_file_id) - files (file_id, file_path, file_name) ``` I would like to create MySQL VIEW with these columns: ``` - news_view (news_id, news_title, news_file_id, news_file_path) ``` But, if `news_file_id > 0` then, i get the path of file (`file_path + file_name`) else `news_file_path` should be `0` How can i do this VIEW? EDIT but what if i want to add two more tables : ``` categories (category_id, category_name) users(user_id, user_name) ``` and table `news` changed to ``` news (news_id, news_title, news_file_id, news_author_id, news_category_id) ``` and view should show only posts with specifed `news_author_id`, `news_category_id`, but `news_file_path` like in the original question? Thanks, Paul.

Original source