Hello Ask Tom Team. Hope you have a wonderful year.
I have three tables. Table A, B and C.
Table A (parent) has one to one relationship with B and C (children).
Requirement:
Select three columns (column a from table A, column b from Table B and column c from table C). A fouth column is required that contain the string 'Yes' for each row from table A found in table C, else 'No'.
What is the best way to get the results?
Thanks in advanced.
If it is a one-to-one relationship, a simple outer join would suffice, eg
select a.a, b.b, c.c, nvl2(c.c,'Yes','No') indicator
from A, B, C
where a.col1 = b.col
and a.col2 = c.some_col(+)
If there could be lots of rows in C that match, hence the above would return lots of rows, then you could use a scalar subquery, eg
select a.a, b.b, c.c, ( select case when count(*) = 1 then 'Yes' else 'No' end from c where some_col = a.col2 and rownum = 1 )
from A, B
where a.col1 = b.col