select value if exists or default from another table

Question

I have a table of user preferences - 'pr_gantt_config' and a table of default values for all the configurable elements of the chart - 'pr_gantt'. I was hoping this query would return either the user expressed the preference or the default value from pr_gantt for all configurable values but I only get the rows where the user has expressed preference. I know I could store a value for each user against each value, but that feels inefficient.

    SELECT `code`, 
       `pref`, 
       `type`, 
       Ifnull(`pref`, `pr_gantt`.`default_value`) AS `pref` 
FROM   `pr_gantt_config` 
       LEFT JOIN `pr_gantt` 
              ON ( `pr_gantt_config`.`gantt_id` = `pr_gantt`.`id` ) 
WHERE  `pr_gantt_config`.`user_id` = '1' 

Your help greatly appreciated.

Answer 1

A work arround would be to first create a view of both tables.

CREATE VIEW temp AS
SELECT code, pref, type,pref, pr_gantt.default_value  FROM pr_gantt_config 
LEFT JOIN pr_gantt ON (pr_gantt_config.gantt_id = pr_gantt.id) WHERE 
pr_gantt_config.user_id = '1'

and then select with IFNULL

SELECT code,type, IFNULL(pref, pr_grannt.default_value) FROM temp