title: Selecting a field in a subquery to be part of outer query.Sql Server 2008
body: <p>How can I select a field that is in a where not exists subquery into the main query.
  These 2 tables are not related in terms of foreign keys etc and I have not control over.</p>
  
  <p>Let's take a fictious sample I have 2 tables  "Category-Order"</p>
  
  <pre><code>    CREATE TABLE [dbo].[Category](
          [Id] [bigint] NOT NULL,
          [Name] [varchar](255) NULL,
          [OrderName] [varchar](50) NULL,
          [CategoryType] [varchar](50) NULL
  
      CREATE TABLE [dbo].[Order](
          [OrderId] [bigint] NOT NULL,
          [OrderName] [varchar](50) NULL
      ) 
  </code></pre>
  
  <p>Given a query like below ,How can I rewrite the below query so that I can have CategoryType Selected as well?
  At the moment I get the following error:</p>
  
  <p><strong>"The multi-part identifier "C.CategoryType" could not be bound."</strong></p>
  
  <pre><code>        INSERT INTO Category(Name,OrderName,CategoryType)
          SELECT DISTINCT 'Fruit',O.OrderName , C.CategoryType
          FROM [Order]O
          WHERE NOT EXISTS(
              SELECT 1
              FROM Category c
              WHERE c.Name='Fruit'
                              AND C.OrderName=O.OrderName)
  </code></pre>
  
  <p>Or could you tell me the equivalent of the above query using a join and eliminating the where not exists</p>
  
  <p>Many thanks for any suggestions</p>