Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Problem With Distinct clause

    Hi

    I have the following tables:

    Suppliers:
    SupplierID int not null primary key auto_increment
    SubscriptionTypeID int not null
    SubscriptionExpDate Date

    Products:
    ID int not null primary key auto_increment
    SupplierID int not null as foreign key
    ProductCode varchar(30) not null
    productName varchar(255) not null
    ImageID int not null as foreign key

    Images:
    ImageID int not null primary key auto_increment
    Image blob not null

    Whats is the correct SQL syntax to retrieve the disticnt Image and ProductName where ProductName is LIKE some user defined string and SubscriptionExpDate >= Date() order by SubscriptionTypeID DESC.

    The above query will retrieve multiple records for the same Image if there are multiple suppliers for that product. How do I retrieve the distinct Image? The only other way i can think of solving this is to redesign the table and include the productName in the Images table. However all images are unique but product names supplied by the supplier for a particular image/product can be different. hence to increase the posibility of retrieving a match I have included it in the products Table!

    Any help will be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "the distinct image" mean?

    if a single product is supplied by multiple suppliers, and you have a query where you join product to suppliers, then why aren't you asking about returning "the distinct product" too?

    that is where your difficulty lies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name. Hence I want to retrieve distinct name as well. But that is not possible becuase the product name for a given product is different for each supplier yet it is for the same product. i dont think there is any other way round this than the way I metioned. By including product name in the images table aswell!

    You may be wondering what is the purpose of such a query. well it is to be implemented on a web page. The above query should display all items which match the search string but onyl display unique images and any one name. At present I get duplicate images and names per row.

    If you have any other suggestions I would be glad to hear them.
    Last edited by ozzii; 04-17-07 at 15:34.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's the important part of what you just said --
    Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name.
    so my question is, which one? the item with the shortest name? the supplier with the latest registration date?

    to pick one from among many, you need a way to say which one

    answers that are not allowed include "any one," "the first one," and "you pick one"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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