SQL Statement for view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Statement for view

Author

Topic

jdfultonii
Starting Member

3 Posts

Posted - 2007-03-21 : 23:12:15

I need to create a sql statement for a view. Here is my requirements.

First table is an actual table with two columns.
Column1 : nvarchar No duplicates
Column2 : int duplicates

Second table is a view that has one column. The value in this column is the same as Column2 above but with no duplicates.

I need to create a view where the following applies. So I am thinking some OUTER JOIN. I know how to do it in ORACLE but not SQL 2005.

The view would return Column1 values when the column2 value is not found in the second table.

Probably pretty simple.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 23:19:04

[code]
select Column1
from [FirstTable] f
where not exists (select * from [SecondTable] s where s.Column2 = f.Column2)
[/code]

[code]
select f.Column1
from [FirstTable] f LEFT OUTER JOIN [SecondTable] s
on f.Column2 = s.Column2
where s.Column2 is not null
[/code]

jdfultonii
Starting Member

3 Posts

Posted - 2007-03-21 : 23:32:57

Ok that seems to work but I have one issue before i cant test fully. I need to join 4 tables into one. I only need one column from each table and the column from each table is the same. The data in the columns are integers and are unique in all tables.

So basically I want all values in columname from table1, table2, table3, table4 into one table(view).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 23:38:16

you can use UNION or UNION ALL depending on you want duplicate value or not.


select col from table1
union 
select col from table2
union 
select col from table3
union 
select col from table4

jdfultonii
Starting Member

3 Posts

Posted - 2007-03-21 : 23:43:42

Thanks soo much khtan. Works like a charm. I am new to using the SQL backend for storing data in views.. It is soo much easier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources