SQL View: Join tables without causing the data to duplicate on every row?

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?

Here's the scenario:

• Table 1 = Accounts: Account Names and Account ID's
• Table 2 = Properties: Property Description, Property Address, and Account ID's
• Table 3 = Vehicles: Vehicle Make, Vehicle Model, and Account ID's

The data looks something like this:

[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

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.

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.

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

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.

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

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.

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.

Can anyone clue me in to how to produce the desired view above that shows nulls instead of duplicates?

This 开发者_StackOverflow中文版is on SQL 2008 R2 Enterprise.

You'd need to use a UNION (actually UNION ALL) in this case.

select a.name as account_Name, 
       p.description as property_DESCRIPTION, 
       p.address as property_ADDRESS, 
       null as vehicles_DESCRIPTION,
       null as vehicles_MAKE, 
       null as vehicles_MODEL
    from Accounts a
        inner join Properties p
            on a.accountid = p.accountid
UNION ALL   
select a.name as account_Name, 
       null as property_DESCRIPTION, 
       null as property_ADDRESS, 
       v.description as vehicles_DESCRIPTION,
       v.make as vehicles_MAKE, 
       v.model as vehicles_MODEL
    from Accounts a
        inner join vehicles v
            on a.accountid = v.accountid

Where it simply doesn't display data on the row if that data did not come the column that it was currently querying.

This doesn't make sense to me (in the sense that I don't understand the meaning, not that it's nonsensical).

The reason that you're seeing the data that you are in the second is that there is no relation between vehicle and property. What you have is this:

Vehicles *-----| Account | -----* Properties

An account has many vehicles, and an account has many properties; there's no relation between vehicle and property, though, so if you were to do something like this:

select
    *

from account a

join property p on p.accountid = a.accountid
join vehicle v on v.accountid = a.accountid

Your view will contain a row for every combination of vehicle and property within a given account (which it looks like you're seeing).

If what you're looking for is a list of accounts with a list of properties and a list of vehicles (with no relation between them), then you need to create two separate queries and combine them with a union.

select
    a.accountid,
    p.property_name,
    null as vehicle_name

from account a

join property p on p.accountid = a.accountid

union

select
    a.accountid,
    null as property_name,
    v.vehicle_name

from account a

join property p on p.accountid = a.accountid
join vehicle v on v.accountid = a.accountid