title: SQL: Select rows in a table by filtering multiple columns from the same table by a 3 column select result
body: <p>I have a table where I want to filter all rows that have a Code,Life and TC equal to the results of a select query on the same table filtered by ID</p>
  
  <pre><code>ID  Code|Life|TC|PORT
  62  XX101   1   1   1
  63  XX101   1   1   2
  64  AB123   1   1   1
  65  AB123   1   1   2
  66  AB123   1   1   3
  67  CD321   1   1   1
  68  CD321   1   1   2
  </code></pre>
  
  <p>This is the best I have come up with but it doesn't seem to be very efficient.</p>
  
  <pre><code>select ID from #table
  where Code = (Select Code from #table where ID = @Port1) and 
        Life = (Select Life from #table where ID = @Port1) and 
        TC = (Select TC from #table where ID = @Port1) 
  </code></pre>