SQL View: join tables without causing duplication of data in each row?

Question

Whenever I create a view from three tables, it duplicates all the data in all rows. I don’t understand how to join them so that it displays zeros instead of duplicates. Can you point me in a direct direction?

Here's the script:

• Table 1 = Accounts: Account Names and Account ID
• Table 2 = Properties: Property Description, Property Address, and Account ID
• Table 3 = Vehicles: car make, vehicle model and account ID

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 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 the records in the database. For each row, I would like to show the account name and then the data from the tables.

, : , , , .

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

, , , , , .

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

, , ; . , . , , , .

, , 1, 2, 3. ; .

- , , ?

SQL 2008 R2 Enterprise.

Answer 1

UNION ( UNION ALL).

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

Answer 2

, , .

( , , ).

, , , , . :

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

, ; , , - :

select
    *

from account a

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

( , ).

( - ), 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