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

Question

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 is on SQL 2008 R2 Enterprise.

Solution

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

Comments

[Philip Kelley]

Beat me to it. I'd recommend a UNION ALL, as there will never be duplicates, so don't have SQL waste time checking for them. Depending, @Christopher might want to use left outer join, if some accounts might not have properties or vehicles.

[Joe Stefanelli]

@Philip: Got the UNION ALL covered. Agree on the left join.

[Admin]

Wow...that query does 3 things that I've never seen before. The union and declaring a column as null makes perfect sense. THANK YOU! In those 'from' statements, however, you're using some magic letters that I need to learn more about. When you say "From Accounts a", what is the a? It's almost like it's assigning the Accounts table to a variable? Instead of "on a.accountid" could you say "on Accounts.accountid"? I changed the query to use the full table name instead of the "a." "p." "v." and it seems like it still works. Is that shorthand? Thank you so very much.

[Admin]

Assuming you see my question as evidence of my knowledge level on SQL; I wonder if you could recommend some additional reading that you think might lift me up to the next level? Perhaps a book that would have given me the knowledge to answer this question had I read it? (Also, since I'm new to server, it won't let me vote up the answer. Otherwise I would. thanks again.)

[Joe Stefanelli]

@Christopher: Yes, the "a" is called a table alias and is, as you surmised, simply a shorthand way of referring to the table at other locations in the query.

[Admin]

Adam thank you; your explanation helps me think through it more clearly. I tried to vote it up but apparently I don't yet have enough reputation to do so.

[Joe Stefanelli]

@Christopher: I'd recommend starting with Itzik Ben-Gan's Microsoft SQL Server 2008 T-SQL Fundamentals. He's a well-known SQL Server authority and an excellent writer.