title: String split column and join to another table
body: <p>Let's say I have 2 tables like this : </p>
  
  <p><strong>Job Offers:</strong></p>
  
  <pre><code>+----+------------+------------+
  | ID |    Name    | Categories |
  +----+------------+------------+
  |  1 | Programmer | 1,2        |
  |  2 | Analyst    | 3          |
  +----+------------+------------+
  </code></pre>
  
  <p><strong>Categories:</strong></p>
  
  <pre><code>+----+-----------------+
  | ID |      Name       |
  +----+-----------------+
  |  1 | Programming     |
  |  2 | Web Programming |
  |  3 | Analysis        |
  +----+-----------------+
  </code></pre>
  
  <p>We've got a string split that takes a string, a delimiter and returns a table, my problem is I'm really not sure how to integrate the table in my query to join the job offers and the categories.</p>
  
  <p>My thinking is that it would be something like this : </p>
  
  <pre><code>SELECT
      O.[ID]                  AS OfferID,
      O.[Name]                AS OfferName,
      CAT.[CategoryName]      AS CategoryName,
      CAT.[CategoryID]        AS CategoryID
  FROM
      JobOffers AS O
  LEFT JOIN
  (
      SELECT 
          O.[ID]              AS OfferID,
          C.[CategoryID]      AS CategoryID,
          C.[Name]            AS Name
      FROM
          (
              SELECT *
              FROM [dbo].[Split](O.[Categories], ',')
          ) AS CJ
      LEFT JOIN
          [Categories] AS C
          ON C.CategoryID = CJ.items  
  ) AS CAT ON CAT.OfferID = O.[ID]
  </code></pre>
  
  <p>Currently I have two errors saying:</p>
  
  <ul>
  <li><code>multi-part identifier O.[ID] cannot be bound</code> </li>
  <li><code>multi-part identifier O.[Categories] cannot be bound</code> </li>
  <li><code>incorrect syntax near AS</code> (last line)</li>
  </ul>
  
  <p>So clearly the problem is how I construct my subquery.</p>