1

I have 3 tables in my database: sProduct, sProductDetail and sProductDetailWarehouse. This is basically a webshop with having multiple EANs possible for a single product. For instance a t-shirt with multiple colors available, each color being it's own EAN.

The important bits about tables:

sProduct has ID which is primary key and title (varchar). sProductDetail has ID (primary key), ID_sProduct (the correlation to the sProduct table), EAN and title sProductDetailWarehouse has ID (primary key), ID_sProductDetail (correlation to the detail table) and stock (int).

What I would want is to use something similar to this:

select pd.ID,pd.title,pdw.stock from sProduct p 
inner join sProductDetail pd on pd.ID_sProduct=p.ID 
left join sProductDetailWarehouse pdw on pdw.ID_sProductDetail=pd.ID

and only have it return 1 record on join with the highest stock. The problem is I can't use order by since I have multiple products in a query needing to be ordered by their release date.

So basically out of every one sProduct.ID I would need only one sProductDetail.ID returned even though there might be many. Can anyone help with this?

Thanks.

Bostjan
  • 1,397
  • 3
  • 21
  • 36
  • 3
    Deleted my sleepy answer. Try here: http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column – Alain Collins Jun 02 '13 at 16:12

0 Answers0