CODEWITHSUNDEEP
oracle-database
Joseph
Joseph

Reputation: 522

Oracle SELECT Subquery

I have two tables. One for transaction list and one for reference

Transaction:


ID | Agency ID | Advertiser ID | Code
1  | 123       | 440           | samplecode

Reference:

ID  | LongName    | Type
123 | Agency1     | Agency
440 | Advertiser1 | Advertiser

How can I write the SQL Subquery in Oracle such that I can include the LongName and the Type in the SELECT statement in the transaction table so that it will look like this:

ID | Agency ID | LongName | Type   | Advertiser ID | LongName    | Type       | Code
1  | 123       | Agency1  | Agency | 440           | Advertiser1 | Advertiser | samplecode

Upvotes: 0

Views: 48

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You may join Transaction to Reference twice:

SELECT
    t.ID,
    t."Agency ID",
    r1.LongName AS ln1,
    r1.Type AS type1,
    t."Advertiser ID",
    r2.LongName AS ln2,
    r2.Type AS type2,
    t.Code
FROM Transaction t
INNER JOIN Reference r1
    ON t."Agency ID" = r1.ID
INNER JOIN Reference r2
    ON t."Advertiser ID" = r2.ID

Upvotes: 2

Related Questions