在MySQL中连接两个具有多个字段排序的表

Question

I have two tables and I have to join it. table structures are

tblproducts --> id(int,pk), name(varchar)

tblphotos ---> id(int,pk), productid(int,fk), photo(varchar), display_order(int)

There can be more than one photos for each product, I need to get one photo with lowest display_order.

I need, tblproducts.id, tblproducts.name, tblphotos.photo(photo with least display_order) also I need to get the whole list as tblproducts.id in ascending order.

Answer 1

Select tblproducts.id, tblproducts.name, (Select Top 1 tblphotos.photo FROM tblphotos, tblproducts Where tblphotos.productid = tblproducts.id Order By display_order)
From tblproducts, tblphotos
Where tblproducts.id = tblphotos.productid
Order by  tblproducts.id

EDIT: Oppss! I didn't see mysql tag. This is for MSSQL by the way.

Yes this query looks like repeating. I should delete Where clause.

Select tblproducts.id, tblproducts.name, (Select Top 1 tblphotos.photo FROM tblphotos, tblproducts Where tblphotos.productid = tblproducts.id Order By display_order)
From tblproducts
Order by  tblproducts.id