CODEWITHSUNDEEP
sqloracle-database
Hawk
Hawk

Reputation: 5170

Self Join with on different tables

I wrote the following query:

SELECT R1.RELATIONSHIP_ID, R1.SUPPLIER_ACCOUNT_ID, R2.BUYER_ACCOUNT_ID
FROM RELATIONSHIP R1 JOIN RELATIONSHIP R2
ON (R1.RELATIONSHIP_ID = R2.RELATIONSHIP_ID)
ORDER BY SUPPLIER_ACCOUNT_ID;

For the tables:
1. RELATIONSHIP: Columns: RELATIONSHIP_ID, SUPPLIER_ACCOUNT_ID, BUYER_ACCOUNT_ID ...
2. ACCOUNT: ACCOUNT_ID, XX_ACCOUNT_ID ....

and gives the result correctly so far

result sampe

However, what I want is more complicated. I need to replace the second and third columns with a column called XX_ACOUNT_ID to be shown twice. Each SUPPLIER_ACCOUNT_ID and BUYER_ACCOUNT_ID has a unique XX_ACCOUNT_ID and a unique ACCOUNT_ID. XX_ACCOUNT_ID belongs to the table ACCOUNTS in which there is ACCOUNT_ID (SUUPLIER_ACCOUNT_ID & BUYER_ACCONT_ID are referenced from this column) and XX_ACCOUNT_ID. I'm a bit confused that does it need nested self-join or multiple inner joins? or subqueries to solve this?

Upvotes: 0

Views: 394

Answers (2)

Yehuda Shapira
Yehuda Shapira

Reputation: 8630

Perhaps I misunderstood the question, but I don't see why you need a self join:

SELECT R.Relationship_Id,
    Supplier.xx_account_id sup_id,
    Buyer.xx._account_id buy_id
FROM Relationship AS R
JOIN Accounts AS Buyer
    ON R.Buyer_account_Id = Buyer.Account_Id
JOIN Accounts AS Supplier
    ON R.Supplier_account_Id = Supplier.Account_Id

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26353

If R1.Supplier_Account_ID and R2.Buyer_Account_ID will always exist in the referenced tables, multiple inner joins should work. Try something like this:

SELECT R1.RELATIONSHIP_ID, AcctSupp.XX_ACOUNT_ID, AcctBuyer.XX_ACOUNT_ID
FROM RELATIONSHIP R1
JOIN RELATIONSHIP R2 ON (R1.RELATIONSHIP_ID = R2.RELATIONSHIP_ID)
JOIN Accounts AcctSupp ON R1.SUPPLIER_ACCOUNT_ID = AcctSupp.Account_ID
JOIN Accounts AcctBuyer ON R2.BUYER_ACCOUNT_ID = AcctBuyer.Account_ID
ORDER BY SUPPLIER_ACCOUNT_ID;

If there's any chance the values won't exist in the referenced tables, use left joins instead:

SELECT R1.RELATIONSHIP_ID, AcctSupp.XX_ACOUNT_ID, AcctBuyer.XX_ACOUNT_ID
FROM RELATIONSHIP R1
JOIN RELATIONSHIP R2 ON (R1.RELATIONSHIP_ID = R2.RELATIONSHIP_ID)
LEFT JOIN Accounts AcctSupp ON R1.SUPPLIER_ACCOUNT_ID = AcctSupp.Account_ID
LEFT JOIN Accounts AcctBuyer ON R2.BUYER_ACCOUNT_ID = AcctBuyer.Account_ID
ORDER BY SUPPLIER_ACCOUNT_ID;

Upvotes: 4

Related Questions