CODEWITHSUNDEEP
sqloracle-database
Timofey Pasichnik
Timofey Pasichnik

Reputation: 23

Oracle query with subquery from another table in "SELECT"

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

Upvotes: 0

Views: 4500

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Pirate
Pirate

Reputation: 1175

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;

Upvotes: 1

Related Questions