title: How to use If condition in self join for a table with multiple columns maintaining hierarchy
body: <p>I have two tables. And I am trying to use self join on table on different fields. Below are the tables available and required output.</p>
  <pre><code>1. Table 1:  time_setting
  
  cid pid   fid    time
  4   220   1138    40
  4   220   NULL    30
  4   NULL  NULL    70
  
  2. Table 2: file_params
  
  fid    pid    cid
  1138   220    4
  1139   220    4
  1140   220    4
  1141   221    4
  </code></pre>
  <p>I want below output:</p>
  <pre><code>fid    tat
  1138   40
  1139   30
  1140   30
  1141   70
  </code></pre>
  <p>As 1138 <code>fid</code> is matching <code>tat</code> taken as 40.
  <code>fid</code> 1139 and 1140 is not matching in table <code>time_setting</code> but its <code>pid</code> is matching so <code>tat</code> taken 30 and for <code>fid</code> 1142 <code>pid</code> is not matching but <code>cid</code> is matching so <code>tat</code> taken as 70.</p>
  <p>I have tried below query:</p>
  <pre><code>SELECT fid,pid,cid,tat FROM
  (SELECT t1.fid,t2.`pid`, t3.cid,
  IF(t1.`fid` IS NOT NULL,t1.`tat`,
  IF(t1.`fid` IS NULL AND t2.pid IS NOT NULL,t2.tat,
  IF(t1.`fid` IS NULL AND t2.`pid` IS NULL,t3.tat,0)))
   AS tat FROM 
  `file_param` p
  LEFT JOIN `time_setting` t1 ON (t1.fid = p.fid)
  LEFT JOIN `time_setting` t2 ON (t2.pid = p.pid)
  INNER JOIN `time_setting` t3 ON (t3.cid = p.cid)
  WHERE p.cid = 4) tbl GROUP BY fid,pid,cid
  </code></pre>
  <p>but not getting expected output.</p>