title: SQL View: Join tables without causing the data to duplicate on every row?
body: <p>Whenever I create a view of 3 tables, it duplicates all data on all rows.  I'm not figuring out how to join these so that it shows nulls instead of duplicates.  Can you point me in the right direct?</p>
  
  <p>Here's the scenario:</p>
  
  <ul>
  <li>Table 1 = Accounts:  Account Names and Account ID's</li>
  <li>Table 2 = Properties: Property Description, Property Address, and Account ID's</li>
  <li>Table 3 = Vehicles: Vehicle Make, Vehicle Model, and Account ID's</li>
  </ul>
  
  <p>The data looks something like this:</p>
  
  <pre><code>[Table 1= Accounts]
     id name  accountid
  
     1 Family A  account001
     2 Family B  account002
     3 Family C  account003
  
  
  
  [Table 2= Properties]
    id accountid  description address
  
    1 account001 home california
    2 account001  beach mexico
    3 account002  hideout arizona
    4 account002  getaway nevada
    5 account002  skilodge idaho
    6 account 003  home texas
  
  
  [Table 3= Vehicles]
  
   id description make model accountid
    1 green  Acura Integra  account001
    2 blue  Aston Martin Vantage account001
    3 silver  Audi Quattro  account001
    4 work  Bently Continental GTC account002
    5 kids  Ford Fusion Hybrid account002
    6 Mom's Car  Land Rover LR4 account003
    7 Paper Weight Mini Clubman account003
    8 Beater  Dodge Caliber  account003
    9 Why Mahindra TR40 account003
    10 Kids  Scion xB  account003
  </code></pre>
  
  <p>My desire is to create a view that shows me all records in the DB.  For each row I'd like to show the account name and then the data from the tables.</p>
  
  <p>I'm looking for the view to return results that look like the following:  Where it simply doesn't display data on the row if that data did not come the column that it was currently querying.</p>
  
  <pre><code>account_Name | property_DESCRIPTION | property_ADDRESS | vehicles_DESCRIPTION   vehicles_MAKE | vehicles_MODEL
  
  - Family A  home    california  **null  null    null**
  - Family A  beach   mexico  **null  null    null**
  - Family A  **null  null**  blue    Aston Martin    Vantage
  - Family A  **null  null**  silver  Audi    Quattro
  - Family B  hideout arizona **null  null    null**
  - Family B  getaway nevada  **null  null    null**
  - Family B  skilodge    idaho   **null  null    null**
  - Family B  **null  null**  kids    Ford    Fusion Hybrid
  </code></pre>
  
  <p>But instead, it displays duplicates of data, like the following where every time it finds a new records, it populates the row with data from other tables.</p>
  
  <pre><code>account_Name    property_DESCRIPTION    property_ADDRESS    vehicles_DESCRIPTION    vehicles_MAKE   vehicles_MODEL
  
  - Family A  home    california  green   Acura   Integra
  - Family A  beach   mexico  green   Acura   Integra
  - Family A  home    california  blue    Aston Martin    Vantage
  - Family A  beach   mexico  blue    Aston Martin    Vantage
  - Family A  home    california  silver  Audi    Quattro
  - Family A  beach   mexico  silver  Audi    Quattro
  - Family B  hideout arizona work    Bently  Continental GTC
  - Family B  getaway nevada  work    Bently  Continental GTC
  - Family B  skilodge    idaho   work    Bently  Continental GTC
  - Family B  hideout arizona kids    Ford    Fusion Hybrid
  - Family B  getaway nevada  kids    Ford    Fusion Hybrid
  - Family B  skilodge    idaho   kids    Ford    Fusion Hybrid
  </code></pre>
  
  <p>Why this is occurring seems to make sense to me; it's all in the joins.  It's as though joins feel like they really need to display data no matter the cost. Thinking from a scripting point of view, it seems like instead of joins, I need to loop through table individually and then join the resulting loops together.</p>
  
  <p>In essence I need the view to first show all records from table 1, then all records from table 2, then all records from table 3.  I don't really want to join this data together; I just want to see it all within a single view.</p>
  
  <p>Can anyone clue me in to how to produce the desired view above that shows nulls instead of duplicates?</p>
  
  <p>This is on SQL 2008 R2 Enterprise.</p>