Issues while creating a view using SQL Server Management Studio 2017

Create a view vw_contacttypedetails for

a. ContactTypeId, Name from person.contacttype

b. BusinessEntityId, PersonId from person.businessentitycontact

c. Join both the tables, resulting in the final display of 4 columns (2 from each table as mentioned)

d. THEN, run a query to see the data in vw_contacttypedetails

I attempted but got an invalid object name. Below is my query:

create view vwcontacttypedetails
as
select ContactTypeID,Name,BusinessEntityID,PersonID
from tblContacttype
join tblbusinessentitycontact
on Contacttype.ContactTypeID = businessentitycontact.ContactTypeID
728x90

1 Answers Issues while creating a view using SQL Server Management Studio 2017

It seems your join statement is wrong...

create view vwcontacttypedetails
as
select c.ContactTypeID,b.Name,b.BusinessEntityID,b.PersonID
from tblContacttype c
join tblbusinessentitycontact b
on c.ContactTypeID = b.ContactTypeID

For how the syntax of joins works, have a look here:
https://www.w3schools.com/sql/sql_join.asp

And from the official Microsoft documentation:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017

Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins. A simplified ISO FROM clause join syntax is:

FROM first_table join_type second_table [ON (join_condition)]

join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition defines the predicate to be evaluated for each pair of joined rows. The following is an example of a FROM clause join specification:

FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
     ON (ProductVendor.BusinessEntityID = Vendor.BusinessEntityID)

The following is a simple SELECT statement using this join:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'
GO

And for your next question have a look here: https://stackoverflow.com/help/how-to-ask

4 months ago