Oracle SQL select within select

Question

So I have reason identified as some kind of ID in table1. I need to write query which writes out the belonging meaning of a reason from table 2. I hope my bad english was enough to explain my problem.

table1

    id  reason
  --------------
    1     1
    2     2
    3     1

table2

domain         value  meaning
------------------------------
table1.reason    1    example1
table1.reason    2    example2

I tried this select but i got error "ORA-01427: single-row subquery returns more than one row" but if I add max() on t2.meaning it only returns me meaning example2 because max value = 2

select 
    t1.id
    (select t2.meaning
    from table2 t2, table1 t1
    where t2.value = t1.reason
    and t2.domain = 'table1.reason') as reason
from table1;

How can i solve this problem?

Please provide sample data and desired results. The error seems quite clear. — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-02-13T13:42:26

I'm pretty sure you just want a correlated subquery, not a join:

select t1.id
       (select t2.meaning
        from table2 t2
        where t2.value = t1.reason and
              t2.domain = 'table1.reason'
       ) as reason
from table1;

Oracle SQL select within select

1 Answers