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 code

create table product(id int ,name varchar(10))
insert into product 
select 1,'特公鸡' union  
select 2,'细米' union
select 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'union
select 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.jpg
2           细米         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 code

select * 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
)