Results 1 to 5 of 5

Thread: Sql Query Help

  1. #1
    Join Date
    Dec 2004
    Posts
    55

    Unanswered: Sql Query SELECT DISTINCT Help

    I would really like some help on a query.

    I have 3 tables

    1. Manufacture Table - contains info about manufacturers
    * mfr_id
    * mfr_name
    * mfr_address

    2. Products Table - contains info about manufacturers products. An manuf may have more than one product and we use the mfr ID to link the product to the manuf.
    * product_id
    * mfr_id
    * product_name
    * cat_id1
    * cat_id2
    * cat_id3
    * newest

    3. Category Table - contains info on the categories and their names
    * cat_id
    * cat_name

    Now newest is a integer. Higher the value the newer the product is.

    I want an SQL query to get one product per mfr and the product should be the one with the highest newest value for a category.

    The current Query I use is... say for category id 5 its

    SELECT DISTINCT mfr_id, mfr_name, mfr_address, product_id, product_name FROM Manufacture, Products WHERE Products.mfr_id = Manufacture.mfr_id AND (cat_id1=5 OR cat_id2=5 OR cat_id3=5)

    The problem with the above query is that I am getting more than one product for each manufacturer

    Any help will be highly appreciated.
    Last edited by oldie123; 12-20-04 at 16:55.

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    You'll need to clarify something for me. If what we want is ONE product per manufacturer, what role does the Category table play? Or do we really want one product per category per manufacturer?
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like a dicey design....

    Can you post some Sample Data and what the expected results should be...DDL would be nice as well
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2004
    Posts
    55
    Quote Originally Posted by Chopin
    You'll need to clarify something for me. If what we want is ONE product per manufacturer, what role does the Category table play? Or do we really want one product per category per manufacturer?
    Category is a table which stores the different categories an product can fall into. Each product can fall into max 3 categories (cat_id1,cat_id2, cat_id3).

    For a given category I need to get one product from each manufacturer.

    I can post sample data later

    Also if the design is dicey how can I improve or fix it.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My guess is, that if you have to rely on an IDENTITY to tell you what the most recent product is, That is not a good model

    You could have a column that's a datetime which would indicate the "newest" one.


    So you're looking for the latest product for each manufacturer?


    This should do

    Code:
    SELECT mfr_id, mfr_name, mfr_address, product_id, product_name 
      FROM Manufacture m JOIN Products p1
        ON p.mfr_id = m.mfr_id 
     WHERE (cat_id1=5 OR cat_id2=5 OR cat_id3=5)
       AND EXISTS ( SELECT mfr_id
    		  FROM Products p2 
    		 WHERE p1.mfr_id = p2.mfr_id
    	      GROUP BY mfr_id
    		HAVING p1.Product_id = MAX(p2.Product_Id))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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