CODEWITHSUNDEEP
sqlsql-server
allendks45
allendks45

Reputation: 341

How to create View in SQL Server by using WHERE clauses

I'm supposed to create a view named VIEW2 on a training database that includes the following fields: 

project number, 
project name, 
employee number, 
employee last name, 
job description.

Of course this is multiple tables in the FROM statement and I'm supposed to use WHERE clauses to join the tables together.

I'm able to create this easily using SSMS and the JOIN statements but now have been limited to criteria that requires using WHERE clauses to create the JOINS and is confusing the heck out of me. Is it even possible?

Upvotes: 0

Views: 4835

Answers (1)

Andomar
Andomar

Reputation: 238068

While you can use a where clause to join tables:

select  *
from    Projects p
,       ProjectEmployes pe
,       Employees e
where   p.id = pe.project_id
        and e.id = pe.employee_id

It is better to use the on clause instead:

select  *
from    Projects p
join    ProjectEmployes pe
on      p.id = pe.project_id
join    Employees e
on      e.id = pe.employee_id

The on syntax keeps tables and their relations near eachother. That is even more pronounced when you join more than three tables!

The on clause also seperates relation conditions and filter conditions. If you use a join, the on clauses have the relations, and the where clause has the filter.

Upvotes: 2

Related Questions