Select Statement on Two different views

Question

I couldn't find the answer to my specific question anywhere.

Is it possible to select from two different views? For example my code looks something like this right.

select view1.col1, view1.col2, view1.col3
from dbo.view1
inner join ~~~~~
inner join ~~~~~

but I want to include a column from a different view in the same query like this.

select view1.co1, view1.col2, view1.col3, view2.col1
from dbo.view1,
     dbo.view2 (this is the line im not sure is possible or allowed)
inner join ~~~~~~
inner join ~~~~~

Is it possible to use the different views as sources for my one select statement or is that not possible in SQL?

I am using SQL server as my database engine.

Avoid syntax like `from dbo.view1, dbo.view2`. This is an ANSI-89 `JOIN` and should be replaced with the more verbose ANSI-92 style `JOIN`. — Patrick Tucci, Jun 22 '15 at 14:40
Why did I get a -1 for this question? Just so I can improve on my other questions in the future. — Jon, Jun 22 '15 at 15:23
what is your Database engine? SQL Server or MYSQL or something else — HaveNoDisplayName, Jun 24 '15 at 15:34
I believe it is SQL Server, but this problem's already been resolved — Jon, Jun 24 '15 at 16:18

HaveNoDisplayName · Accepted Answer · 2015-06-22T14:48:31.313

2

Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other.

Just treat two different views as like two different tables when using in SELECT Clause.

SELECT vw1.a, vw2.b
FROM View1 vw1 INNER JOIN View2 vw2
     ON vw1.id = vw2.id

For Clarification, A view can be thought of as either a virtual table or a stored query. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.

EDIT

Select vw1.username, vw2.usertype 
From vw1 INNER JOIN vw2 
         ON vw1.colID = vw2.colID  <<<< Here you have to use 
                                   <<<< common column between two views to matched rows

edited Jun 22 '15 at 14:48

answered Jun 22 '15 at 14:38

HaveNoDisplayName

8,291
106
37
47

So what if the column exists in only one of the views and I want to add it to my resulting query? So like this: `select vw1.username, vw2.usertype from vw1.users ` how do I include vw2 so I can get usertype? – Jon Jun 22 '15 at 14:45
in this case, you have use Join between vw1 and vw2 on common column, and then use vw2.usertype in select query. See Edit – HaveNoDisplayName Jun 22 '15 at 14:47
@Jon:- Which database you are using? – HaveNoDisplayName Jun 22 '15 at 14:53
This solution worked, but I'm having trouble understanding why you can reference vw2 before it's actually joined on line 2. Does that kind of declaration order not matter like it would in a language like C? – Jon Jun 22 '15 at 14:53
1

C and SQL are two different languages, and both have different syntax to do the stuff.. You can use this https://msdn.microsoft.com/en-us/library/ms187956.aspx and https://msdn.microsoft.com/en-us/library/h09t6a82(v=vs.80).aspx to understand more about view – HaveNoDisplayName Jun 22 '15 at 14:54
1

One more think, you are talking about this one Select vw1.username, vw2.usertype , here vw2 specified, but actual execution start from FROM clause, not from SELECT – HaveNoDisplayName Jun 22 '15 at 14:58
Yes, that was the answer I was looking for. I just wasn't sure where the execution started from. – Jon Jun 22 '15 at 15:00

score 1 · Answer 2 · answered Jun 22 '15 at 14:39

1

When it comes to SELECT, a view works just a regular (base) table. JOIN as before:

select view1.co1, view1.col2, view1.col3, view2.col1
from dbo.view1
inner join dbo.view2 ON ~~~~~
inner join ~~~~~~
inner join ~~~~~

answered Jun 22 '15 at 14:39

jarlh

42,561
8
45
63

The Reason · Answer 3 · 2015-06-22T14:51:01.903

1

You should use Join

select view1.col1, view1.col2, view1.col3, view2.newColumn
from dbo.view1
inner join dbo.view2 on <key> = <key>;

this is exapmle only to clear your understanding as i see from your example

from dbo.view1,
     dbo.view2 (this is the line im not sure is possible or allowed)
inner join ~~~~~

you cant understand how it works, my exapmle above its this Join and this form below with old technic what you should avoid

  select view1.col1, view1.col2, view1.col3, view2.newColumn
   from dbo.view1, dbo.view2
   where view1.key = view2.key

Thanks and good luck

edited Jun 22 '15 at 14:51

answered Jun 22 '15 at 14:40

The Reason

7,705
4
24
42

You should avoid ANSI-89 `JOIN`s. This syntax is deprecated. http://stackoverflow.com/questions/1613304/ansi-joins-versus-where-clause-joins – Patrick Tucci Jun 22 '15 at 14:43
Yeap i know that why i wrote "You should use Join" – The Reason Jun 22 '15 at 14:44
@jarlh Sorry, I should have specified, ANSI-89 joins have been depecated in Microsoft SQL Server since 2008. Other RDBMSs may be more lenient. I agree with you though, bad habit regardless. – Patrick Tucci Jun 22 '15 at 14:51
It is not a advice it's only explanation, as you can see in code above he doesnt know what is the deference and how it works `from dbo.view1, dbo.view2 (this is the line im not sure is possible or allowed) inner join ~` – The Reason Jun 22 '15 at 14:53
@The I wouldn't include it at all. Microsoft is disabling some of the ANSI-89 `OUTER JOIN` functionality in SQL Server 2012 and will likely continue the process. I know there are other RDBMSs out there that allow then ANSI-89 `JOIN` still, but there are many advantages to using the ANSI-92 `JOIN`. Unless someone asks what the old syntax means, I don't think as a community we should keep digging it back up. – Patrick Tucci Jun 22 '15 at 14:55

Select Statement on Two different views

3 Answers3

Linked