Lupiyo Lupiyo - 4 months ago 9
SQL Question

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

I'll be very fast.

Problem: My subquery is returning more than one row (and this is ok, because it should returns more than one, or just one), and trigger the ora error 1427, closing the query results.

Question: How can I show all the registers of this subquery?

Notes


  • All my searches on internet says that you can limit by 1, but this is not what I really want.

  • D table is like a matrix, I give FIELD4 with a specific value, that returns FIELD to me. And in the same query, I have another value to FIELD4, and returns another type and business result on FIELD.






SELECT (SELECT B.Value
FROM TABLE_B B
WHERE B.FIELD1 = A.FIELD1
AND B.FIEL2 = A.FIELD2
AND B.FIELD3 = A.FIELD3
AND B.Category= 'Adress') AS RESULT_FIELD,
(SELECT B.Value
FROM TABLE_B B
WHERE B.FIELD1 = A.FIELD1
AND B.FIEL2 = A.FIELD2
AND B.FIELD3 = A.FIELD3
AND B.Category= 'Gender') AS RESULT_FIELD2
FROM TABLE_A A;


Tables example:



Table A

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 one
4 1 Adress Avenue Two
5 2 Adress Avenue Eleven





As you can see, It has more than one register on B, and I need to show on the result table all Adresses, considering the condicional that I pass on Category colunm.

Code updated*

The result should be like:

ID Name Age Gender Adress
1 Lapras 6 Female Avenue one
1 Lapras 6 Female Avenue two
2 Lincon 45 Male Avenue eleven

Answer

Getting your desired results (per your example input/output) requires JOIN to be used, plus some logic for the multiple categories. Here is one possible example using the two categories you referenced (gender and adress):

SELECT A.ID, A.Name, A.Age, B1.Value as Gender, B2.Value as Address
FROM TABLE_A A
LEFT OUTER JOIN TABLE_B B1 ON B1.A_ID = A.ID AND B1.Category = 'Gender'
LEFT OUTER JOIN TABLE_B B2 ON B2.A_ID = A.ID AND B2.Category = 'Adress';

Notice that you end up with the Cartesian product of Category values. For example, if someone had two values entered for the first category (maybe not for Gender but another one) and two values for the second one, you would get 4 results back - a result for each permutation (2 categories x 2 values in each category). Even so, this style of query seems to be what you're looking for.