title: Joining multiple columns in one table to a single column in another table
body: <p>I am looking to create a view that pulls data from two tables "Schedule" and "Reference".</p>
  
  <p>Schedule has 50+ columns (it's almost completely denormalized -- not my design), most of which contain a value that could be joined to a column in the Reference table.</p>
  
  <p>How do I write the SQL statement to correctly join each column in Schedules to the single column in Reference?</p>
  
  <p>The Schedule table is defined as:</p>
  
  <pre><code>    CREATE TABLE [dbo].[Schedule](
      [ID] [int] NOT NULL,
      [SCHEDULEWEEK] [datetime] NOT NULL,
      [EMPNO] [numeric](10, 0) NOT NULL,
      [EMPLNAME] [varchar](32) NULL,
      [EMPFNAME] [varchar](32) NULL,
      [EMPSENDATE] [datetime] NULL,
      [EMPHIREDATE] [datetime] NULL,
      [EMPTYPE] [char](1) NULL,
      [EMPSTATUS] [char](1) NULL,
      [SNREFUSALS] [tinyint] NULL,
      [QUALSTRING] [varchar](128) NULL,
      [JOBOVERSHIFTTYPE] [bit] NULL,
      [SHORTNOTICE] [bit] NULL,
      [SHORTNOTICEWAP] [bit] NULL,
      [SHORTNOTICEPHONE] [varchar](32) NULL,
      [LEADHAND] [bit] NULL,
      [DUALCURRENCY] [bit] NULL,
      [MIN100WINDOW] [bit] NULL,
      [STATHOLIDAY] [bit] NULL,
      [AREAOVERHOURS] [bit] NULL,
      [DOUBLEINTERZONES] [bit] NULL,
      [MAXDAYSPERWEEK] [tinyint] NULL,
      [MAXHOURSPERWEEK] [numeric](10, 2) NULL,
      [MAXHOURSPERSHIFT] [numeric](10, 2) NULL,
      [MAXDOUBLESPERWEEK] [tinyint] NULL,
      [ASSIGNEDDAYS] [tinyint] NULL,
      [ASSIGNEDHOURS] [numeric](10, 2) NULL,
      [ASSIGNEDDOUBLES] [tinyint] NULL,
      [ASSIGNEDLOAHOURS] [numeric](10, 2) NULL,
      [SHIFTNO1] [int] NULL,
      [TEXT1_1] [varchar](64) NULL,
      [TEXT2_1] [varchar](64) NULL,
      [DAYFLAG1] [bit] NULL,
      [COMMENT1] [text] NULL,
      [SHIFTNO2] [int] NULL,
      [TEXT1_2] [varchar](64) NULL,
      [TEXT2_2] [varchar](64) NULL,
      [DAYFLAG2] [bit] NULL,
      [COMMENT2] [text] NULL,
      [SHIFTNO3] [int] NULL,
      [TEXT1_3] [varchar](64) NULL,
      [TEXT2_3] [varchar](64) NULL,
      [DAYFLAG3] [bit] NULL,
      [COMMENT3] [text] NULL,
      [SHIFTNO4] [int] NULL,
      [TEXT1_4] [varchar](64) NULL,
      [TEXT2_4] [varchar](64) NULL,
      [DAYFLAG4] [bit] NULL,
      [COMMENT4] [text] NULL,
      [SHIFTNO5] [int] NULL,
      [TEXT1_5] [varchar](64) NULL,
      [TEXT2_5] [varchar](64) NULL,
      [DAYFLAG5] [bit] NULL,
      [COMMENT5] [text] NULL,
      [SHIFTNO6] [int] NULL,
      [TEXT1_6] [varchar](64) NULL,
      [TEXT2_6] [varchar](64) NULL,
      [DAYFLAG6] [bit] NULL,
      [COMMENT6] [text] NULL
  -- Snip
  ) ON [PRIMARY]
  </code></pre>
  
  <p>And the Reference table is defined as: </p>
  
  <pre><code>CREATE TABLE [dbo].[Reference](
      [ID] [int] NOT NULL,
      [CODE] [varchar](21) NOT NULL,
      [LOCATIONCODE] [varchar](4) NOT NULL,
      [SCHAREACODE] [varchar](16) NOT NULL,
      [LOCATIONNAME] [varchar](32) NOT NULL,
      [FLTAREACODE] [varchar](16) NOT NULL
  ) ON [PRIMARY]
  </code></pre>
  
  <p>I am trying to join each [TEXT1_<em>]/[TEXT2_</em>] column in Schedule to the [SCHAREACODE] column in reference.  All the reference table contains is a list of areas where the employee could work.</p>