title: How can I compare two columns against a combination of two columns in a table in Oracle?
body: <p>I have something of a logical puzzle I can not figure out.</p>
  
  <p>I have a table with two columns representing a PK. Prods and Prod_Colour</p>
  
  <pre><code>TableA
  Prods, Date_, Prod_Colour
  
  One  |  null     |  Red
  One  |  null     |  Blue
  Two  | 2012-06-08| Blue
  Two  |   null    | Yellow
  Three|   null    | Green
  Three| 2012-06-08| Red
  </code></pre>
  
  <p>The date repesents that those Prods are no longer available from that date however the date can be changed once it becomes available again.</p>
  
  <p>I am capable of restricting rows based on date.</p>
  
  <pre><code>SELECT a.*
  FROM TABLEA a
  WHERE Date_ &gt; '2012-06-11' or Date_ IS NULL
  </code></pre>
  
  <p>but what I need to do is use this table in a subquery where both Prods and Prod_Colour are combined in the exclusion... </p>
  
  <p>that's not a good explanation but basically in the example above Prods Three where Prod_Colour = 'Red' and Prods Two where Prod_Colour = 'Blue' would be excluded but Prods One would be included for both Prod_Colour where date was > than today or null.</p>
  
  <p>As always I appreciate any advice or hints.</p>
  
  <p>Thanks in advance.</p>
  
  <p>To try and clarify further I have a query that selects from several different tables, among one of those tables I need to include prods and prod_Colour only where prods and Prod_Colour (combined as a unique identifier for each row) are available in TableA  where date_ indicates they are available. The combinations won't change but the date_ can. I would use tDate to indicate today as the date to compare against.</p>
  
  <pre><code>Select O.Prods, O.Prod_Colour /*each combination needs to match the combination in TableA */
  FROM TableB
  Where O.Prods + O.Prod_C in (Select A.Prods + A.Prod_Colour
                               FROM TABLE A WHERE Date_ IS NULL or Date_ &gt; tDate)
  </code></pre>
  
  <p>something like that.  </p>