title: how to create a new column based on where condition
body: <p>I want to display additional columns based on my where conditions. My where conditions based on type. But when I use the following case when condition, I seem to retrieve one row with the hard-coded value and one null row for every match. The non-match have 1 null row. I cant seem to figure out what I have wrong in my case when condition</p>
  
  <pre><code>select t1.id,   
    case when   
    t2.value like 'App%'  
    then 'Fruit'  
    end as 'Fruit'  
    case when 
    t2.value like 'Car%'  
    then 'Veggie'  
    end as 'Veggies'  
    case when 
    t3.value = 'Skittles' 
    then 'Candy'  
    end as 'Candy' 
  where t1.id = t2.id  
  and t1.id=t3.id  
  </code></pre>
  
  <p>T1:</p>
  
  <pre><code>ID   
  1  
  2  
  3  
  4   
  5    
  6    
  7  
  8  
  9  
  </code></pre>
  
  <p>T2:  </p>
  
  <pre><code>ID(fk)   value  
  1        Apple  
  1        Orange  
  2        Carrot  
  3        Berry  
  3        Melon 
  </code></pre>
  
  <p>T3:   </p>
  
  <pre><code>ID (fk) value  
  4        Mars
  5        Twix
  6        Skittles
  7        Milkyway
  </code></pre>
  
  <p>Desired Output:  </p>
  
  <pre><code>ID         Fruits     Veggies       Candy    
  1          Fruit  
  2                      Veggie  
  3                            
  4                                      
  5                                      
  6                                    Candy  
  7                                       
  8                       
  9
  </code></pre>
  
  <p>Current incorrect Output:</p>
  
  <pre><code>ID         Fruits     Veggies       Candy    
  1          Fruit    
  1  
  2                      Veggie    
  2  
  3                            
  4                                       
  5                                     
  6                                    Candy   
  6  
  7     
  8                       
  9
  </code></pre>