Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Wisconsin
    Posts
    1

    Question Unanswered: 3 small table database. Query: Return All Suppliers, Offering All Product, Except...

    I have broken my question down into simpler terms using only 3 small tables, but the idea is the same. Well let me get to the problem (=.

    Pretend we have just a small
    database with 3 tables (Tb_Supplier, Tb_Product,
    Tb_Offers)
    Our problem is as follows:
    Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.

    Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.

    CREATE TABLE Tb_Supplier (
    Supp_ID [bigint] IDENTITY PRIMARY KEY,
    Name [char] (10) NOT NULL ,
    )

    CREATE TABLE Tb_Product (
    Prod_ID [bigint] IDENTITY PRIMARY KEY,
    Name [char] (10) NOT NULL ,
    )

    CREATE TABLE Tb_Offers (
    Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
    Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
    Quantity [decimal](18, 0) NULL ,
    Price [money] NULL
    )

    The query I'm trying to solve is to return ALL
    supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.

    Here is what I have tried/ my thoughts. I first tried breaking it
    into parts and seeing if I could solve them. For instance, I wanted
    to return all suppliers NOT offering computers, cars, or tvs. I
    accomplished that with the following query.

    SELECT Name
    FROM Tb_Supplier
    WHERE NOT EXISTS (SELECT *
    FROM Tb_Offers, Tb_Product
    WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
    AND (Tb_Product.Name='computer'
    OR Tb_Product.Name='car'
    OR Tb_Product.Name='tv'))

    (also wrote it using the NOT IN statement)

    SELECT Name
    FROM Tb_Supplier
    WHERE Supp_ID NOT IN
    (SELECT DISTINCT Supp_ID
    FROM Tb_Offers, Tb_Product
    WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
    AND (Tb_Product.Name='computer'
    OR Tb_Product.Name='car'
    OR Tb_Product.Name='tv'))


    I don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)

    The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).


    Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively

    And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)

    Our Offers Table contains the following

    Supp_ID Prod_ID
    111 10
    111 13

    222 11
    222 13
    222 14

    333 13
    333 14
    333 15

    444 14
    444 15



    I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.

    I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda

    I hope I am explaining this well. Any reply is greatly appreciated. Thanks!

    (Oh and yes this is just Microsoft SQL Syntax)

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Try the following:

    First create a query of the products you want to show.

    SELECT Prod_ID, [Name]
    FROM Tb_Products
    WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])

    Once this query is created, create the new query using this query instead of Tb_Products
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is a most interesting problem

    let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)

    in other words, a given supplier can offer a given product only once

    (this is important because we'll be counting rows without using DISTINCT)

    the number of products each supplier supplies is given by --
    Code:
        select Supp_ID, count(*) 
          from Tb_Offers 
         group by Supp_ID
    the total number of products is --
    Code:
      select count(*) from Tb_Products
    the suppliers which supply all products are --
    Code:
        select Supp_ID
          from Tb_Offers 
         group by Supp_ID
        having count(*) = 
               ( select count(*) from Tb_Products )
    now for the tricky part, excluding three certain products

    first, let's figure out which product IDs they have --
    Code:
        select Prod_ID 
          from Tb_Product
         where Name in ('computer','car','tv')
    now if a supplier supplies one of these three "excluded" products,
    let's count a 1, and for any of the other products, let's count a 0 --
    Code:
        select Supp_ID
             , sum( case when Prod_ID 
                      in (
                           select Prod_ID 
                             from Tb_Product
                            where Name in ('computer','car','tv')
                         ) then 1 else 0 end  
                  ) as excluded_product_count
           from Tb_Offers          
         group by Supp_ID
    notice how the subquery inside the CASE is not correlated,
    which means that it will be extremely efficient

    the suppliers we want are those with an "excluded product count" of 0

    furthermore, the count of all the products they do supply has to be
    3 less than the total number of products

    so here is the final query --
    Code:
        select Supp_ID
          from Tb_Offers          
         group by Supp_ID
        having sum( case when Prod_ID 
                      in (
                           select Prod_ID 
                             from Tb_Product
                            where Name in ('computer' ,'car', 'tv')
                         ) then 1 else 0 end  
                  ) = 0
           and count(*) = 
               ( select count(*) from Tb_Products ) - 3
    i'm fairly confident in this, but have not tested it

    please let me know how it works for you


    rudy
    http://r937.com/
    http://rudy.ca/

  4. #4
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59

    Relational Division

    CreativeSoul,

    What you are after is called relational division.

    Celko has an article that you will find very helpful...

    http://www.dbazine.com/celko1.html

    Please read this first and then look at this query....There are several approaches to achieving this in SQL...but this is usually the easiest to understand.....

    SELECT S.Supp_ID
    FROM Tb_Supplier S
    INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
    WHERE O.Prod_ID NOT IN(10,11,12)
    GROUP BY S.Supp_ID
    HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))

Posting Permissions

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