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.

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

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.

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"