CODEWITHSUNDEEP
sqloracle-database
dee
dee

Reputation: 609

How can I compare two columns against a combination of two columns in a table in Oracle?

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_ > '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_ > tDate)

something like that.

Upvotes: 0

Views: 8466

Answers (2)

DCookie
DCookie

Reputation: 43533

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_ > tDate OR date_ is NULL);

Upvotes: 7

SQLCurious
SQLCurious

Reputation: 524

SELECT a.*
FROM TABLEA a
WHERE (Date_ > '2012-06-11' or Date_ IS NULL)
AND not (Prods = 'Two' and Prod_Colour = 'Blue')
AND not (Prods = 'Three' and Prod_Colour = 'Red')

Upvotes: 0

Related Questions