conditional join

[XtrEM3]

ok so i have the following query...

SELECT * FROM product_offers

each offer has either a merchantID or a sellerID. merchant names and seller names are each located in a different table, here are their structures...

product_offers_merchant: MerchantId, Name, etc. (the rest are not relevant)
product_offers_seller: SellerId, SellerName, etc. (the rest are not relevant)

so how can i get the names of either the merchant or the seller without making extra queries?

any help is appreciated

[longneck]

if you mean that a product offer has EITHER a merchant OR a seller, but never both, then this query should do it for you:

select po.*, coalesce(m.name, s.sellername) as name
from product_offers po
left join product_offers_merchant m on
 m.merchantid = po.merchantid
left join product_offers_seller s on
 s.sellerid = po.sellerid

but if a product_offer can have both (or none) then you will need this:

select po.*, m.name as merchantname, s.sellername as sellernamename
from product_offers po
left join product_offers_merchant m on
 m.merchantid = po.merchantid
left join product_offers_seller s on
 s.sellerid = po.sellerid

[XtrEM3]

exceptional!

thanks again longneck