CODEWITHSUNDEEP
joinoracle11g
zundarz
zundarz

Reputation: 1594

How to query table to get one row requiring two joins to two separate ID?

Oracle 11g

PERSON table contains both seller and buyer ID. How can I get buyer and seller into single result set? I can get results to list either the buyer or the seller but not both.

Person
=================
 PersonID First   Last
  1      Joe      Camel
  2      Ronald   McFly
  3      Barbara  Wawa

 SalesDetail
 =========================
 TransID   Amount  SellerID CustomerID
 98         500     1        2
 99         700     3        1

Desired Result
===========================================
SellerID    SellerLast  BuyerID BuyerLast  Amount
1           Camel       2       McFly      500
3           Wawa        1       Camel      700

Upvotes: 0

Views: 101

Answers (2)

Gratzy
Gratzy

Reputation: 9389

try this

select seller.sellerid, 
       seller.last,
       buyer.buyerid,
       buyer.last, 
       amount
from
person buyer
inner join salesdetail on buyer.personid = salesdetail.cutomerid
inner join person seller on salesdetail.sellerid = seller.personid

unable to test myself at the moment

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231741

Just join to the Person table twice

SELECT sd.sellerID,
       seller.last sellerLast,
       sd.buyerID,
       buyer.last buyerLast,
       sd.amount
  FROM salesDetail sd
       JOIN person seller ON (sd.sellerID = seller.personID)
       JOIN person buyer  ON (sd.buyerID  = buyer.personID)

You may want outer joins if it is possible that either the buyer or the seller is unknown.

Upvotes: 1

Related Questions