CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Iakovos Belonias
Iakovos Belonias

Reputation: 1373

Create a view combining two tables ms sql server 2014

I need to create a view combining two tables: Task and Status

What I did was:

CREATE VIEW todolist
select     tasks.taskname, 
           tasks.description 
FROM  status 
INNER JOIN tasks 
ON  status.statusid = tasks.statusid 
WHERE ( status.statusid = 1)

But they told me that the above example is wrong and the right answer is:

CREATE todolist AS VIEW SELECT tasks.taskname, 
           tasks.description 
FROM  status 
INNER JOIN tasks 
ON  status.statusid = tasks.statusid 
WHERE ( status.statusid = 1)

Which don't make sense since the last example have wrong syntax to create the view, I mean the first line:

CREATE todolist AS VIEW SELECT tasks.taskname,

Which is the right answer?

Upvotes: 0

Views: 177

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

First, I would recommend using table aliases so the query is easier to write and to read:

CREATE VIEW todolist AS
    SELECT t.taskname, t.description 
    FROM status s INNER JOIN
         tasks t
         ON s.statusid = t.statusid 
    WHERE s.statusid = 1;

(Your version missed the AS.) More importantly, the JOIN is not needed. You are only using columns from the tasks and the WHERE is on the JOIN column.

So, the better way to write this is:

CREATE VIEW todolist AS
    SELECT t.taskname, t.description 
    FROM tasks t
    WHERE t.statusid = 1;

Note: This assumes that statusid is unique in status, but that seems like a very reasonable assumption.

If you are learning SQL, you should be learning how to write queries correctly and concisely.

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5550

What you need is:

CREATE VIEW todolist 
AS 
SELECT tasks.taskname, tasks.description 
FROM  status 
INNER JOIN tasks 
ON  status.statusid = tasks.statusid 
WHERE ( status.statusid = 1)

The full syntax is described here

Upvotes: 1

Related Questions