Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Creating a View with Data from Multiple Tables

By: FYIcenter.com

(Continued from previous topic...)

Can You Create a View with Data from Multiple Tables?

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderView AS
   SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,
      c.FirstName, c.LastName, c.CompanyName
   FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c
   WHERE o.CustomerID = c.CustomerID
GO

SELECT TOP 10 SalesOrderNumber, TotalDue, CompanyName 
   FROM SalesOrderView;
GO
SalesOrderNumber TotalDue    CompanyName
---------------- ----------- ------------------------------
SO71915          2361.6403   Aerobic Exercise Company
SO71938          98138.2131  Bulk Discount Store
SO71783          92663.5609  Eastside Department Store
SO71899          2669.3183   Coalition Bike Company
SO71898          70698.9922  Instruments and Parts Company
SO71902          81834.9826  Many Bikes Store
SO71832          39531.6085  Closest Bicycle Store
SO71776          87.0851     West Side Mart
SO71797          86222.8072  Riding Cycles
SO71895          272.6468    Futuristic Bikes

(Continued on next topic...)

1. What Are Views?
2. How To Create a View on an Existing Table?
3. How To See Existing Views?
4. How To Drop Existing Views from a Database?
5. How To Get a List of Columns in a View using "sys.columns"?
6. How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
7. How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
8. How To Generate CREATE VIEW Script on an Existing View?
9. How To Get the Definition of a View Out of the SQL Server?
10. Can You Create a View with Data from Multiple Tables?
11. Can You Create a View using Data from Another View?
12. What Happens If You Delete a Table That Is Used by a View?
13. Can You Use ORDER BY When Defining a View?
14. How To Modify the Underlying Query of an Existing View?
15. Can You Insert Data into a View?
16. Can You Update Data in a View?
17. Can You Delete Data from a View?
18. How To Assign New Column Names in a View?
19. How Column Data Types Are Determined in a View?
20. How To Bind a View to the Schema of the Underlying Tables?
21. How To Create an Index on a View?