CODEWITHSUNDEEP
sqloracle-databaseouter-join
Sassa Moustafa
Sassa Moustafa

Reputation: 17

exclude some rows from my outer join result set

i have one table called products and the other one called prices.

Now i am trying to to show all products with and without prices, so i am using a left join. So far so good.

select * from products,prices where products.id = prices.id(+)

The prices table does also have a validFrom and a validTo record. What i want to achieve now is to exclude the expired records(first two rows). Is this possible when i am using left join

this is the result of the query.

ResultSet: ResultSet

With

SELECT *
FROM   products
       LEFT JOIN prices
            ON   products.id = prices.id
                 AND (prices.validfrom >= sysdate
                      OR prices.validfrom IS NULL)
                 AND (prices.validto < sysdate
                      OR prices.validto IS NULL);

I get enter image description here

expected result however should be: enter image description here

Upvotes: 0

Views: 306

Answers (3)

Nikhil
Nikhil

Reputation: 3950

you will better have to use inner join for it not left outer join;

Upvotes: 0

admdev
admdev

Reputation: 478

Try this:

SELECT * FROM (select * from products,prices where products.id = prices.id(+)) WHERE (prices.validTo IS NULL OR prices.validTo > sysdate)

Upvotes: 0

sticky bit
sticky bit

Reputation: 37482

Add a check, that prices.validfrom <= sysdate AND prices.validto > sysdate to the ON clause.

SELECT *
       FROM products
            LEFT JOIN prices
                      ON products.id = prices.id
                         AND (prices.validfrom <= sysdate
                               OR prices.validfrom IS NULL)
                         AND (prices.validto > sysdate
                               OR prices.validto IS NULL);

You should also use the explicit LEFT JOIN syntax instead of that ancient WHERE ... (+) thing.

Upvotes: 1

Related Questions