SPIDERMAN wrote:
> Hi gurus,
>
> I have 3 tables and their fields 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
>
>
select buyers.BuyerID as buyerid, sellers.SellerID as sellerid,
decode(min(buyerssellers.goldorsilver), 'G', 'yes', 'no') as gold,
decode(max(buyerssellers.goldorsilver), 'S', 'yes', 'no') as silver
from Buyers, Sellers, BuyersSellers
where Buyers.BuyerID=BuyersSellers.BuyerID(+) and
Sellers.SellerID(+)=BuyersSellers.SellerID
group by buyers.buyerid, sellers.sellerid
Received on Wed Jul 09 2003 - 15:29:58 CDT
