title: How do I retrieve aliased items and/or items in SQL
body: <p>I have a table of items in  SQL Server 2008 which are setup as follows:</p>
  
  <p>ITEMS TABLE</p>
  
  <pre><code>itemId (PK) | Ref 
  1           | item1
  2           | item2
  </code></pre>
  
  <p>I also have a table to store aliases of these items:</p>
  
  <p>ALIASES TABLE</p>
  
  <pre><code>aliasId(PK) | objectId(FK) | AliasOfId(FK) | Ref 
  10          | 50           | 1             | A1
  20          | 51           | 2             | A2
  30          | 52           | 2             | A3
  </code></pre>
  
  <p>I am trying to produce a query to so that I can retrieve a list of items but I'm having problem in that I can only seem to get either items OR aliases not both (problem I'm having is LEFT JOIN always joins to aliases.</p>
  
  <p>SQL used is below. Any ideas, appreciate any help?</p>
  
  <pre><code>SELECT  
        ISNULL(A.objectId,I.itemId) AS itemId 
       ,ISNULL(A.ref,I.ref) AS ref
       ,isAlias = CASE WHEN A.aliasID IS NULL THEN 1 ELSE 0 END
  
  FROM        items I
  LEFT JOIN   aliases A ON (I.itemId = A.AliasOfId)
  WHERE       
        ISNULL(A.objectId,I.itemId) = 1
     OR ISNULL(A.objectId,I.itemId) = 51
     OR ISNULL(A.ref,I.ref) = 'A3'
  </code></pre>
  
  <hr>
  
  <p>I had put data in with the question but it seems to have reformatted it. Here's the full data in SQL 2008. </p>
  
  <pre><code>CREATE TABLE [items]
  ( [itemId] [int] NOT NULL
  , [ref] [varchar](50) NOT NULL )
   ON [PRIMARY]
  
  CREATE TABLE [aliases]
  ( [aliasId] [int] NOT NULL
  , [objectId] [int] NOT NULL
  , [aliasOfId] [int] NOT NULL
  , [ref] [varchar](50) NOT NULL )
   ON [PRIMARY]
  
   INSERT INTO [items] ([itemId],[ref])
   VALUES (1, 'Item1'),(2, 'Item2')
  
   INSERT INTO [aliases] ([aliasId],[objectId],[aliasOfId],[ref])
   VALUES
   (10, 50, 1, 'A1')
  ,(20, 51, 2, 'A2')
  ,(30, 52, 2, 'A3')
  </code></pre>