Oracle query with subquery from another table in "SELECT"

0
votes

I have a table1, and table2. There are sevaral rows in table2 for ID from table1. Example: Table1 (ID, Name, Age):

543 | John | 15
321 | Doe  | 17.

SELECT SCORE 
FROM TABLE2 
WHERE ID = 543

(many rows as response).

I need a query with some columns from table1, as well as first row in column from table2.

Something like that:

SELECT A.NAME NAME,
       A.AGE AGE,
       (SELECT SCORE
        FROM TABLE2 B
        WHERE A.ID = B.ID
        AND ROWNUM = 1) SCORE
FROM TABLE1 A,
     TABLE2 B
WHERE A.ID = B.ID
2
sqloracle
Note that using ROWNUM = 1 could theoretically give you any of the correlated rows from TABLE2. - GriffeyDog
What do you mean by "first" row in column SCORE from TABLE2 (for the ID you are looking at)? How are the rows ordered, so there can be a "first" row, a "second" row, etc.? That "order by" instruction should be included in the correlated subquery, and the filter by ROWNUM would have to be in an outer query, if "first" means anything other than "any" row. - mathguy

2 Answers

1
votes

Limit rows using FETCH as shown here. Before getting result, you may want to order data to get, for example, latest value.

SELECT A.NAME,
       A.AGE,
       (SELECT SCORE
        FROM TABLE2 B
        WHERE A.ID = B.ID
        FETCH FIRST 1 ROWS ONLY
       ) as SCORE
FROM TABLE1 A;
1
votes

Just use a correlated subquery with no join:

SELECT A.NAME,
       A.AGE,
       (SELECT B.SCORE
        FROM TABLE2 B
        WHERE A.ID = B.ID AND ROWNUM = 1
       ) as SCORE
FROM TABLE1 A;

Let me note that there is no such thing as "the first row in a table". Tables represent unordered sets. You need a column to specify the ordering. This returns a value from an arbitrary row. It is offered here because you use the same logic in the question.