Re: sql question

SPIDERMAN wrote:

> Hi gurus,
>
> I have 3 tables as follows:
> 1) Buyers: BuyerID
> 2) Sellers: SellerID
> 3) BuyersSellers: BuyerID, SellerID, GoldorSilver
>
> Where GoldorSilver contains either 'G' for Gold or 'S' for Silver.
>
> The following query gives me all the buyers, which is what I want. But it
> also give me duplicate rows if a buyer buy both Gold AND Silver. If I want
> to reduce the number of rows (so that there aren't rows with similar
> BuyerID) but add extra columns (Gold column and Silver column), how do I
> write this query in Oracle SQL or ANSI99 SQL? Thank you in advance
>
> select BuyerID, SellerID, GoldorSilver from Buyers, Sellers, BuyersSellers
> where Buyers.BuyerID=BuyersSellers.BuyerID(+) and
> Sellers.SellerID(+)=BuyersSellers.SellerID

Hint:
UNION ALL
DECODE

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)