Oracle query with subquery from another table in "SELECT"

Question

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

Note that using ROWNUM = 1 could theoretically give you any of the correlated rows from TABLE2. — GriffeyDog
– GriffeyDog, Commented Dec 2, 2020 at 18:43
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. — user5683823
– user5683823, Commented Dec 2, 2020 at 18:47

Gordon Linoff · Accepted Answer · 2020-12-02 19:07:25Z

2

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.

edited Dec 2, 2020 at 19:07

answered Dec 2, 2020 at 18:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Timofey Pasichnik Over a year ago

Thanks for instant reply. It works for me

Marmite Bomber Over a year ago

Though this is a working answer, I'm always scared if I find this construct in the productive code... It is safe from ORA-01427and will deliver some data.

Pirate · Accepted Answer · 2020-12-02 18:50:52Z

1

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;

answered Dec 2, 2020 at 18:50

Pirate

1,1851 gold badge10 silver badges21 bronze badges

Collectives™ on Stack Overflow

Oracle query with subquery from another table in "SELECT"

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related