SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    conditional join

    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

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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:

    Code:
    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

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    exceptional!

    thanks again longneck


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •