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...)
- What Are Views?
- How To Create a View on an Existing Table?
- How To See Existing Views?
- How To Drop Existing Views from a Database?
- How To Get a List of Columns in a View using "sys.columns"?
- How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
- How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
- How To Generate CREATE VIEW Script on an Existing View?
- How To Get the Definition of a View Out of the SQL Server?
- Can You Create a View with Data from Multiple Tables?
- Can You Create a View using Data from Another View?
- What Happens If You Delete a Table That Is Used by a View?
- Can You Use ORDER BY When Defining a View?
- How To Modify the Underlying Query of an Existing View?
- Can You Insert Data into a View?
- Can You Update Data in a View?
- Can You Delete Data from a View?
- How To Assign New Column Names in a View?
- How Column Data Types Are Determined in a View?
- How To Bind a View to the Schema of the Underlying Tables?
- How To Create an Index on a View?
|