Oracle SQL - Join with sub condition

Question

I have tables in Oracle database: items (id, type, name) and contracts (id, item_id).

I want to select all items that is of type "sale" and have a contract OR all items that is of type "gift" (no matter contract here).

For this, I yped:

SELECT i.id, c.id as contract_id, i.type, i.name FROM items i, contracts c, -- others table WHERE -- some implicit joins AND ( (i.type = 'sale' AND c.item_id = i.id) OR (i.type = 'gift') )

This doesn't work.

How to select products of type "sale" and "gift" and join contracts only if type is "sale"?

Tip of today: Always use modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh
– jarlh, Commented Aug 29, 2019 at 20:08
Show us some sample table data and the expected result - all as formatted text, not images. And take a look at minimal reproducible example too. — jarlh
– jarlh, Commented Aug 29, 2019 at 20:09
Not at all surprised it doesn't work, given that p is not defined. — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2019 at 20:10

forpas · Accepted Answer · 2019-08-29 20:19:00Z

Your query does not work because you are using an INNER JOIN (although with an archaic syntax).
So your 2nd condition:

OR all items that is of type "gift" (no matter contract here)

cannot be applied because the INNER JOIN removes the rows from items that do not have any contract.
Use a LEFT JOIN:

select i.id, c.id as contract_id, i.type, i.name from items i left join contracts c on c.item_id = i.id where (i.type = 'sale' AND c.item_id is not null) OR (i.type = 'gift')

Collectives™ on Stack Overflow

Oracle SQL - Join with sub condition

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related