I've two tables and I must join it. table structures are

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

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

There might be several photos for every product, I have to acquire one photo with cheapest display_order.

I want, tblproducts.id, tblproducts.title, tblphotos.photo(photo with least display_order) also I have to obtain the whole list as tblproducts.id in climbing order.

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 did not see tag. To MSSQL incidentally.

Yes this question appears like repeating. I ought to remove 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

I haven't much understanding of mysql, however in MSSQL it can be done by using query :

SELECT p.id, 
       p.name, 
       (SELECT TOP 1 photo FROM tblphotos WHERE productid = p.id ORDER BY display_order)
FROM tblproducts p
ORDER BY p.id

Please change query for mysql accordingly.

UPDATE :

I want query in mysql like :

SELECT p.id, p.name, 
       (SELECT photo FROM tblphotos WHERE productid = p.id ORDER BY display_order limit 1)
FROM tblproducts p
ORDER BY p.id

I've not examined the next query however it should work, try it out:

SELECT 
    p.id, p.name, f.photo 
FROM
    tblproducts p,
    (
        SELECT productid, MIN(display_order) AS min_display_order 
        FROM tblphoto 
        GROUP BY productid
    ) AS t,
    tblphoto f 
WHERE 
    p.id = t.productid AND 
    t.productid = f.productid AND 
    t.min_display_order = f.display_order 
ORDER BY p.id