Re: sql joints

Originally posted by Spiderman
> Hi gurus,
>
> I have 4 tables as follows:
>
> 1) Buyers: BuyerID
> 2) BuyersSellers: BuyerID, SellerID
> 3) Sellers: SellerID
> 4) Honda: SellerID, ModelID
>
> I want to get all the buyers, regardless whether they buy Honda or
> any other
> 'make' (Ford, BMW, etc.). So I made the query as
>
> select b.BuyerID, s.SellerID, h.ModelID
> from Buyers b, BuyersSellers bs, Sellers s, Honda h
> where b.BuyerID = bs.BuyerId (+) and
> bs.SellerID = s.SellerID (+) and
> s.SellerID = h.SellerID (+) and
> h.ModelID = '007'
>
> but it returned fewer number of records than that from Buyers
> table. I ran
> the following:
>
> select b.BuyerID, s.SellerID
> from Buyers b, BuyersSellers bs, Sellers s, Honda h
> where b.BuyerID = bs.BuyerId (+) and
> bs.SellerID = s.SellerID (+)
>
> it returned the correct number of records.
>
> How can I modify the first query so that it will return the
> correct number
> of records? It seems to me here that the outer joint is good
> only for 2
> consecutive joints (Buyers->BuiyersSellers->Sellers). What's
> wrong? Thanks
> in advance.

This should fix it:

select b.BuyerID, s.SellerID, h.ModelID from Buyers b, BuyersSellers bs, Sellers s, Honda h where b.BuyerID = bs.BuyerId (+) and

          bs.SellerID = s.SellerID (+) and
          s.SellerID = h.SellerID (+) and
          h.ModelID (+) = '007'

Without the (+) on the last condition, you were excluding any buyers who don't buy Hondas (because for them, h.ModelId will be NULL, not '007').

BTW, having a table per make of vehicle is ... shall we say, unusual.

--
Posted via http://dbforums.com