Title: How can I compare two columns against a combination of two columns in a table in Oracle?
Tags: sql;oracle
Question: I have something of a logical puzzle I can not figure out.

I have a table with two columns representing a PK. Prods and Prod_Colour

```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
```

The date repesents that those Prods are no longer available from that date however the date can be changed once it becomes available again.

I am capable of restricting rows based on date.

```SELECT a.*
FROM TABLEA a
WHERE Date_ &gt; '2012-06-11' or Date_ IS NULL
```

but what I need to do is use this table in a subquery where both Prods and Prod_Colour are combined in the exclusion... 

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.

As always I appreciate any advice or hints.

Thanks in advance.

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.

```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)
```

something like that.  


Here is the accepted answer: Based on your last edit, it seems like you are looking for this construct:

```SELECT *
  FROM tableB
 WHERE (prods, prod_colour) IN
       (SELECT prods, prod_colour
          FROM tablea a
         WHERE date_ &gt; tDate OR date_ is NULL);
```

Comment for this answer: This is what I was looking for, You've no idea how convoluted my attempts had become. I'll need to test it thoroughly but initial results look exactly like what I need.

Here is another answer: ```SELECT a.*
FROM TABLEA a
WHERE (Date_ &gt; '2012-06-11' or Date_ IS NULL)
AND not (Prods = 'Two' and Prod_Colour = 'Blue')
AND not (Prods = 'Three' and Prod_Colour = 'Red')
```

Comment for this answer: Thanks for answering SQl, unfortunately I can not exclude through hardcoded values as the dates may change for different rows on different days depending on Prods availability. so tomorrow I may only need to exclude Prods = 'Two' and Prod_Colour = 'Blue'
Comment for this answer: I added some further clairification. I'm working on a larger easier query that shows what I'm trying to achieve and will include it above soon.
Comment for this answer: @dee, You will have to define your problem better, then. What is the general condition that you are filtering for? (All you have given us are examples, so all we can create is hard-coded code.)
Comment for this answer: You will need brackets around the OR condition, otherwise it will be incorrect.
Comment for this answer: @dee: so how do you know which products to exclude based on the date? Is there a validity date stored somewhere?

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2012:00:06/12/12', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><oracle>', 'Title': 'How can I compare two columns against a combination of two columns in a table in Oracle?', 'ViewCount': 7700, 'id': 11001376, 'user_AboutMe': '', 'user_AccountId': 926975.0, 'user_DisplayName': 'dee', 'user_Reputation': 599.0, 'user_Views': 65.0, 'user_id': 957564.0}}