title: Mysql return a default value even if join does not return any records
body: <p>I have 4 tables that I join by using this query below. Given my data below, it wont actually return any because there are no records in the TRANSACTION_LINE with a line object type value of 6.</p>
  
  <p>I want this query to return a default value of key_1 from EX_WORK, null line_Id, default value of 'OFF' on the TENDER_CODE if the query does not return any records.</p>
  
  <p>I already tried coalesce and ifnull but seemed that it did not work. Can this be fixed by doing a left join instead of the inner?</p>
  
  <p>Thanks!</p>
  
  <pre><code>SELECT  w.key_1 as if_entry_no, 
             l.line_id as line_id, 
             o.object_export_code as tender_code
        FROM EX_WORK w,
             transaction_header h, 
             transaction_line l, 
             line_object o 
       WHERE w.key_1 = h.if_entry_no
         AND h.transaction_void_flag in (0,8)
         AND h.if_entry_no = l.if_entry_no
         AND l.line_object_type = 6
         AND l.line_action &lt;&gt; 55 
         AND (l.line_action &lt;&gt; 72 OR h.tender_total = 0)  
         AND l.line_object = o.line_object
  
  EX_WORK
  serial_no   key_1   
  111         2879051 
  
  TRANSACTION_HEADER
  if_entry_no store_no transaction_void_flag tender_total
  2879051     9500     0                     0
  
  TRANSACTION_LINE
  if_entry_no line_Id line_object_type line_object line_action
  2879051     1       14               9109        38
  2879051     2       1                9105        99
  2879051     3       5                9501        98
  2879051     4       11               9111        46
  
  LINE_OBJECT
  line_object line_object_type    resource_id object_export_code
  9105        1                   5529        null
  9109        1                   5533        null
  9111        1                   5535        null
  9501        1                   5709        null
  </code></pre>