creating view from different tables

• kalyanmick

  Newbie

  Points: 7

  More actions

  December 2, 2010 at 2:51 am

  #229350

  hi friends..

  i have two tables table_users and table_Applications.. i need a view to relate both the tables..

  table_users

  userID UserName UserType

  1 kal admin

  2 pra client

  3 sud admin

  4 muk client

  5 son client

  6 raj admin

  7 nee client

  8 ami admin

  9 sur admin

  10 chak client

  table_Applications

  AppId App Name UserID ClientID

  1 app1 9 7

  2 app2 3 2

  3 app3 6 5

  4 app4 3 4

  5 app5 8 2

  6 app6 8 4

  7 app7 6 7

  8 app8 9 2

  9 app9 6 10

  10 app10 1 5

  ths is the view i need.............

  View_Relations

  AppId App Name UserID UserName ClientID ClientName

  1 app1 9 sur 7 nee

  2 app2 3 sud 2 pra

  3 app3 6 son 5 son

  4 app4 3 sud 4 muk

  5 app5 8 ami 2 pra

  6 app6 8 ami 4 muk

  7 app7 6 raj 7 nee

  8 app8 9 sur 2 pra

  9 app9 6 raj 10 chak

  10 app10 1 chak 5 sona

  i use sql express 2005 management studio...... please help me to code this view.. its very urgent requirement for me..

  thanks in advance..

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 2, 2010 at 3:31 am

  #1257773

  Use the following code as a template to prepare a CREATE TABLE and INSERT script, which when run will create the tables and populate them with some sample data.

  CREATE TABLE table_users (userID DATATYPE, UserName DATATYPE, UserType DATATYPE)

  INSERT INTO table_users (userID, UserName, UserType)

  SELECT 1, 'kal', 'admin' UNION ALL

  ...

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden