Reputation: 522
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
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