求一条简略的SQL语句_ASP.NET

求一条简单的SQL语句
有表product(产品表)和imgPath(路径表)
product中字段 id,name.
imgPath中字段 id,foreignId(外键id为product中的id),imgPath
imgPath可以存储一个foreignid的多个图片.
比如数据如下
product
id name
1 特公鸡
2 细米
3 诺鸡鸭
imgPath
id foreignId imgPath
1 1 /images/001.jpg
2 1 /images/002.jpg
3 2 /images/003.jpg
现在取值的时候如何只取一张图片?
即:
1 特供机 /images/001.jpg
2 细米 /images/003.jpg

------解决方案--------------------------------------------------------
select from product p left join imgPath i
on i.foreignId=p.id and id in (select min(id) from imgPath group by foreignId)
------解决方案--------------------------------------------------------
select
name,
(select MIN(imgPath.imgPath) from imgPath where imgPath.foreignId=product.id)
from product
------解决方案--------------------------------------------------------

SQL code

select product.*,B.imgPath from product  OUTER Apply (SELECT TOP 1 * FROM  imgPath WHERE imgPath.foreignId=product.ID ORDER BY imgPath.ID) B
------解决方案--------------------------------------------------------
探讨
还有一个就是 ,如果imgPath中没有数据!一样的要把product中的数据获取到

------解决方案--------------------------------------------------------
SQL codecreate table product(id int ,name varchar(10))insert into product select 1,'特公鸡' union  select 2,'细米' unionselect 3,'诺鸡鸭'  create table imgPath(id int ,foreignid int,imgpath varchar(50))insert into imgPath select 1,1, '/images/001.jpg'union  select 2,1,'/images/002.jpg'unionselect 3,2, '/images/003.jpg'select a.id,a.name,b.foreignid,b.imgpath from product a left join  imgPath b on a.id=b.foreignid where b.id in(select min(id) from imgPath group by foreignId)--id          name       foreignid   imgpath----------- ---------- ----------- --------------------------------------------------1           特公鸡        1           /images/001.jpg2           细米         2           /images/003.jpg
------解决方案--------------------------------------------------------
探讨

引用:

SQL code

select product.*,B.imgPath from product  OUTER Apply (SELECT TOP 1 * FROM  imgPath WHERE imgPath.foreignId=product.ID ORDER BY imgPath.ID) B
Access中可以使用吗?

------解决方案--------------------------------------------------------
探讨

引用:

引用:

SQL code

select product.*,B.imgPath from product  OUTER Apply (SELECT TOP 1 * FROM  imgPath WHERE imgPath.foreignId=product.ID ORDER BY imgPath.ID) B
Access中可以使用吗……

------解决方案--------------------------------------------------------
用这个试试，我也是前两天才知道的。。
SQL codeselect * from product where id in(select foreignId from ( select idd=row_number()over(partition by foreignId order by getdate()),* from imgPath)t where idd=1 order by orderNo desc)