title: Adding row of JOINed to already filtered SELECT results
body: <p>I have a query that is returning some particular data:</p>
  
  <pre><code>  SELECT sub.*
    FROM (
  Select item_id, field_id, attr_val from [CONTRACT_ATTR] where item_id in (select item_id from [CONTRACT_ATTR] where field_id = 326 and attr_val = 'Y')
         ) sub
   WHERE field_id = 234 or field_id = 413
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/q6DmQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q6DmQ.png" alt="enter image description here"></a></p>
  
  <p>The value A750 is associated with the item_pk on the manufacturer table. I want to bring back the item_name from the MFR table and add it as a row for each associated item_id. So 16 would have 3 rows after for example. And the item_name from MRF will be in the attr_val column.</p>
  
  <p>The closest I have right now is this which is pulling in the item_name but on the wrong row and is removing other fields I need.</p>
  
  <pre><code>  SELECT sub.*, m.item_name
    FROM (
  Select item_id, field_id, attr_val from [CONTRACT_ATTR] where item_id in (select item_id from [CONTRACT_ATTR] where field_id = 326 and attr_val = 'Y')
         ) sub
  JOIN MFR m
  ON m.item_pk = sub.attr_val and field_id = 234
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/eqUff.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eqUff.png" alt="enter image description here"></a></p>
  
  <p>What am I doing wrong and how do I fix it?</p>
  
  <p>EDIT:</p>
  
  <pre><code>CREATE TABLE [dbo].[CONTRACT_ATTR](
      [ITEM_ID] [int] NOT NULL,
      [FIELD_ID] [int] NOT NULL,
      [ATTR_VAL] [varchar](500) NOT NULL,
      [UPD_DTT] [datetime] NOT NULL,
      [UPD_USR] [varchar](500) NOT NULL,
   CONSTRAINT [PK_CONTRACT_ATTR] PRIMARY KEY CLUSTERED 
  (
      [ITEM_ID] ASC,
      [FIELD_ID] ASC,
      [ATTR_VAL] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  )
  
  CREATE TABLE [dbo].[MFR](
      [ITEM_ID] [int] IDENTITY(1,1) NOT NULL,
      [ITEM_NAME] [varchar](500) NOT NULL,
      [ADD_DTT] [datetime] NOT NULL,
      [ADD_USR] [varchar](500) NOT NULL,
      [UPD_DTT] [datetime] NOT NULL,
      [UPD_USR] [varchar](500) NOT NULL,
      [ACTIVE_IND] [char](1) NULL,
      [ITEM_PK] [varchar](500) NULL,
   CONSTRAINT [PK_MFR] PRIMARY KEY CLUSTERED 
  (
      [ITEM_ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  )
  </code></pre>