title: How to JOIN multiple tables and return results with NULL values too
body: <p>I'm trying to join 4 tables and one of the tables doesn't have all the matching ID's BUT I still need to show the results of the join and even for the rows that didn't have a corresponding ID. </p>
  
  <p>Here's an example of what I'm talking about:</p>
  
  <p>Example tables:</p>
  
  <pre>
  
      DECLARE @Table1 TABLE (id INT PRIMARY KEY CLUSTERED, ts DateTime, tbl2_id INT, price DECIMAL(4,2), tbl3_id INT, tbl4_id INT)
  
      INSERT INTO @Table1 VALUES(1, '2013-07-25 09:30:00', 10, 10.25, 1);
      INSERT INTO @Table1 VALUES(2, '2013-07-25 10:25:00', 20, 25.25, 1);
      INSERT INTO @Table1 VALUES(3, '2013-07-25 11:45:00', 30, 30.15, 2);
      INSERT INTO @Table1 VALUES(4, '2013-07-25 13:31:00', 40, 80.40, 2);
  
      DECLARE @Table2 TABLE (id INT PRIMARY KEY CLUSTERED, symbol VARCHAR(25), tbl1_id int)
  
      INSERT INTO @Table2 VALUES(10, 'XYZ', 1);
      INSERT INTO @Table2 VALUES(20, 'ABC', 2);
      INSERT INTO @Table2 VALUES(30, 'RST', 3);
      INSERT INTO @Table2 VALUES(40, 'EFG', 4);
  
      DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, exch VARCHAR(25))
  
      INSERT INTO @Table3 VALUES(1, 'A');
      INSERT INTO @Table3 VALUES(2, 'B');
      INSERT INTO @Table3 VALUES(3, 'C');
      INSERT INTO @Table3 VALUES(4, 'D');
  
      DECLARE @Table4 TABLE (id INT PRIMARY KEY CLUSTERED, int tbl1_id, cnt INT)
  
      INSERT INTO @Table4 VALUES(1, 2, 19);
      INSERT INTO @Table4 VALUES(2, 4, 2013);
  </pre>
  
  <p>Example Query:</p>
  
  <pre>
      SELECT tbl1.id, tbl1.ts, tbl2.symbol, IFNULL(tbl3.cnt,0) AS cnt
  
      FROM TABLE1 tbl1
  
      JOIN TABLE2 tbl2
      ON tbl1.tbl2_id = tbl2.id
  
      JOIN TABLE3 tbl3
      ON tbl3.id = tbl1.tbl3_id
  
      LEFT OUTER JOIN TABLE4 tbl4
      ON tbl1.tbl4_id = tbl4.id
  
      WHERE tbl1.ts BETWEEN '2013-07-25 09:30:00 AND '2013-07-25 16:00:00'
      AND tbl1.price >= 15.00
      LIMIT 1000;
  </pre>
  
  <p>So basically what I'm trying to do is if tbl4 doesn't have a tbl1_id I'd still want to see the result from table1 BUT show a 0 value for Cnt...when i run this query I'm getting a bunch of duplicate entries and the data isn't looking right.</p>