How do i create a 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)

How do i create a view

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-11-20 : 12:57:23

Hello,
I have two tables,

1. A user table
2. A user credits table.

Both tables are related by username column, HOw can i get the value of user, firstname, credits and suspension from both tables ?

do i run the query twice ?

The first two columns user, firstname belong to the user table while the last two belong to the credits table.

thank you
MM

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-20 : 13:18:58

SELECT ut.[user],ut.firstname,uc.credits,uc.suspension
FROM [user]as ut INNER JOIN usercredit uc ON ut.username=uc.username

NOTE:
1-query is valid only when column username is present in both tables.
2-you have username and firstname also
3- as "user" is a keyword you have to place "[]" around it.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-20 : 23:38:26

Create View:

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[VW_UserDetails]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[VW_UserDetails]

CREATE VIEW
AS
SELECT ut.[user],ut.firstname,uc.credits,uc.suspension
FROM [user]as ut INNER JOIN usercredit uc ON ut.username=uc.username

But, I think you relate some other unique column in both tables. Because relating username is not good for all cases.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources