title: Subquery or conditional logic for result set
body: <p>Consider the mock tables</p>
  
  <pre><code>ORDERS                          
  OrderID CustID date              
  10       1     2014-01-01       
  11       3     2014-02-01       
  12       2     2014-03-01        
  
  CUSTOMER 
  CustID FName LName
  1      Jon   Doe         
  2      Jane  Doe
  3      Mike  Brown
  
  LINEITEM                        
  OrderID ProdID                  
  10      1                        
  10      3                       
  11      2                       
  12      1
  12      2
  
  PRODUCTS
  ProdID Description 
  1      Apple
  2      Orange
  3      Grape
  </code></pre>
  
  <p>I want to return a result set that looks like this:</p>
  
  <pre><code>OrderID CustomerLastName Apple Orange Grape
  11      Brown            No    Yes    No
  12      Doe              Yes   Yes    No
  </code></pre>
  
  <p>Logic: Select orderID, customer last name, determine if they bought each product (yes or no)
         for all orders with order date greater then 2014-01-01.</p>
  
  <p>This is about as far as I can get</p>
  
  <pre><code>select O.OrderID as 'OrderID', 
         C.LName as 'CustomerLastName', 
        (some conditional or subquery for figuring out yes or no) as 'Apple',
        (some conditional or subquery for figuring out yes or no) as 'Orange',
        (some conditional or subquery for figuring out yes or no) as 'Grape'
  from ORDERS O join CUSTOMER C using (CustID)
                join LINEITEM using (OrderID)
                join Products P using (ProdID)
  where O.date &gt; 2014-01-01;
  </code></pre>
  
  <p>Any help on how to get just one line returned for each order and YES / NO logic for the products would be greatly appreciated.</p>
  
  <p>Thanks</p>