SQL Queries

Discussion in 'OT Technology' started by jdubb5005, Mar 27, 2007.

  1. jdubb5005

    jdubb5005 I live in a giant bucket.

    Joined:
    Feb 15, 2006
    Messages:
    120
    Likes Received:
    0
    Location:
    Green Bay
    Ok I have a query that I am unable to solve, and with the limited data i can give you guys with the databases I am hoping you will be able to elighten me.


    Given the database description below:

    Tb_Supplier(Supp_ID, Name, City)
    Tb_Consumer(Con_ID, Name, City)
    Tb_Product(Prod_ID, Name, MU)
    Tb_Offers(Supp_ID, Prod_ID, Quantity, Price)
    Tb_Requests(Con_ID, Prod_ID, Quantity, Price)
    Tb_Transactions(T_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price)

    Products offered by all suppliers? (first use EXCEPT or MINUS then convert to a portable solution (subquery) and on all 3 systems(mySQL, Oracle, MSSql))



    I just cant seem to get past the idea of a product being offered by all suppliers. I cant grasp the logic to put it into motion.
     
  2. kappe277

    kappe277 New Member

    Joined:
    May 8, 2006
    Messages:
    34
    Likes Received:
    0

    I'm working on the same project he is, and we also need the Name of suppliers having at most one consumer without using the COUNT function if you could be so kind. I will love anyone who helps for the rest of my life
     
  3. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    well, "shipping and handling" is likely a common product. Perhaps that helps you grasp the idea?

    In regards to the 2nd question, how do you link the consumers to their respective suppliers? Can a consumer belong to only one supplier? I see primary keys for both suppliers and consumers, but I do not see a column that would assign a consumer to a given supplier.
     
  4. kappe277

    kappe277 New Member

    Joined:
    May 8, 2006
    Messages:
    34
    Likes Received:
    0
    consumers and suppliers are tied together in the transactions table
     
  5. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    So you've got the suppliers and products tables, how are they related or to ask a different way, is there some connection?? Sure the Offers table. So you should join those 3 tables first.

    THen you have to think about this a little.
    Products offered by all suppliers....hmm...that seems to mean that they only want you to display those products that are offered by all suppliers and suppress the others.

    Ok so that's where minus comes in.

    Here's a quick tutorial on minus:
    http://www.1keydata.com/sql/sql-minus.html

    This is the tricky part and I'm tired so I'm having trouble getting the minus to work in my head. Hopefully this will help you solve it. I'll check back tomorrow, maybe it'll make sense then.
    The logic might be easier to grasp if you think of suppliers being wholesalers instead of manufacturers.
     
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Sounds like you want to COUNT the suppliers, then COUNT the unique entries for a product ID/supplier?
     
  7. jdubb5005

    jdubb5005 I live in a giant bucket.

    Joined:
    Feb 15, 2006
    Messages:
    120
    Likes Received:
    0
    Location:
    Green Bay
    The minus part is where I was having issues, it is easy to get all the products but to subtract the ones not being supplied by all is perplexing.
     
  8. gnp

    gnp New Member

    Joined:
    Mar 11, 2007
    Messages:
    280
    Likes Received:
    0
    Select Tb_Products.Prod_ID From Products
    Minus
    Select Tb_Products.Prod_ID, Tb_Suppliers.Supp_ID From Products Inner Join Tb_Offers On Tb_Products.Prod_ID = Tb_Offers.Prod_ID Left Outer Join Tb_Supplier On Tb_Supplier.Supp_ID = Tb_Offers.Supp_ID Where Supp_ID Is Null

    or some such nonsense :dunno:
     

Share This Page