Select Int to char, two tables

• Alan Brownlow

  Old Hand

  Points: 393

  More actions

  October 10, 2006 at 3:48 am

  #117202

  Hi,

  I have two tables

  Table1_Users

  UserID (int), UserName Char(20)

  Table2_Event

  EventName(char30), RequestBy(int), OwnedBy(int)

  The table2 has a FK from the int columns to the userID.

  I am having problems returning the userName(s) from table1 when asking for the eventname as well.

  Thanks for any advice 

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  October 10, 2006 at 4:13 am

  #664948

  Alan,

  here is this......

  CREATE TABLE Table1_Users

  (

   UserID INT NOT NULL PRIMARY KEY CLUSTERED,

   UserName VARCHAR(20) NOT NULL

  )

  CREATE TABLE Table2_Event

  (

   EventName CHAR(20) NOT NULL,

   RequestBy INT NOT NULL REFERENCES Table1_Users(UserID) ,

   OwnedBy INT NOT NULL REFERENCES Table1_Users(UserID) ,

  )

  INSERT INTO Table1_Users (UserID , UserName)

  SELECT 1,'ABC'

  UNION ALL

  SELECT 2,'DEF'

  UNION ALL

  SELECT 3,'GHI'

  UNION ALL

  SELECT 4,'JKL'

  UNION ALL

  SELECT 5,'MNO'

  INSERT INTO Table2_Event (EventName , RequestBy , OwnedBy)

  SELECT 'Event 1', 1 , 2

  UNION ALL

  SELECT 'Event 2', 4 , 1

  UNION ALL

  SELECT 'Event 3', 5 , 3

  SELECT TE.EventName , TE.RequestBy , TU1.UserName , TE.OwnedBy , TU2.UserName

  FROM Table2_Event TE

    INNER JOIN Table1_Users TU1 ON TE.RequestBy = TU1.UserID

    INNER JOIN Table1_Users TU2 ON TE.OwnedBy = TU2.UserID

   

   

  --Ramesh
  

  ---

• Alan Brownlow

  Old Hand

  Points: 393

  More actions

  October 10, 2006 at 4:17 am

  #664949

  Thanks, Ramesh that did the trick