sql - Single-row subquery returns more than one row - Real solution -

i'll fast.

problem: subquery returning more 1 row (and ok, because should returns more one, or one), , trigger ora error 1427, closing query results.

question: how can show registers of subquery?

notes

• all searches on internet says can limit 1, not want.

• d table matrix, give field4 specific value, returns field me. , in same query, have value field4, , returns type , business result on field.

---

 select (select b.value                 table_b b              b.field1 = a.field1                  , b.fiel2 = a.field2                  , b.field3 = a.field3                  , b.category= 'adress') result_field,          (select b.value                 table_b b              b.field1 = a.field1                  , b.fiel2 = a.field2                  , b.field3 = a.field3                  , b.category= 'gender') result_field2 table_a a; 

tables example:

table     id   name      age     1    lapras     6     2    lincon     45   table b      id   a_id    category      value     1      1       gender      female     2      2       gender      male     3      1       adress      avenue 1     4      1       adress      avenue 2     5      2       adress      avenue eleven 

---

as can see, has more 1 register on b, , need show on result table adresses, considering condicional pass on category colunm.

code updated*

the result should like:

id name    age gender   adress  1  lapras  6   female   avenue 1 one  lapras  6   female   avenue 2 two  lincon  45  male     avenue eleven 

getting desired results (per example input/output) requires join used, plus logic multiple categories. here 1 possible example using 2 categories referenced (gender , adress):

select a.id, a.name, a.age, b1.value gender, b2.value address table_a left outer join table_b b1 on b1.a_id = a.id , b1.category = 'gender' left outer join table_b b2 on b2.a_id = a.id , b2.category = 'adress'; 

notice end cartesian product of category values. example, if had 2 values entered first category (maybe not gender one) , 2 values second one, 4 results - result each permutation (2 categories x 2 values in each category). so, style of query seems you're looking for.