CODEWITHSUNDEEP
databaseoracle-databaseplsql
maephisto
maephisto

Reputation: 5182

Oracle right join 3 tables but only has to match 1

I'm not sure if the title is relevant to the question, bu i didn't found a better one. Here's the problem - I have 4 table, let's say:

products (id, product_name),
priceA(product_id, price),
priceB(product_id, price),
priceC (product_id, price).

What i need to do is write a select that will show me only products that have prices in priceA OR priceB OR priceC OR in any combination of a,b,c

How could I do this ? Thank you!

Upvotes: 0

Views: 82

Answers (2)

bidifx
bidifx

Reputation: 1650

You could try using a UNION to combine the results:

select p.id,
   p.name,
   a.price
FROM products p INNER JOIN pricea a ON p.id = a.product_id
UNION
select p.id,
   p.name,
   b.price
FROM products p INNER JOIN priceb b ON p.id = b.product_id
UNION
select p.id,
   p.name,
   c.price
FROM products p INNER JOIN pricec c ON p.id = c.product_id

See SQLFiddle: http://sqlfiddle.com/#!4/85519/15

Upvotes: 3

Taryn
Taryn

Reputation: 247860

Did you try something like this:

select p.id,
  p.name,
  a.price Price_A,
  b.price Price_B,
  c.price Price_C
from products p
left join pricea a
  on p.id = a.product_id
left join priceb b
  on p.id = b.product_id
left join pricec c
  on p.id = c.product_id
where a.price is not null
  or b.price is not null
  or c.price is not null

See SQL Fiddle with Demo

Upvotes: 2

Related Questions