sql and linq query
Database has tables Photos and PhotoAlbums. I need a query that will select all albums and only one photo from each album. I need SQL and LI开发者_运维百科NQ version of this query. Thanks in advance.
In Linq I'm not that experienced in it so I'll only give the SQL Server Query
SELECT a.*,b.MyPhoto FROM PhotoAlbums a,(SELECT Top 1 myPhoto from Photos Where AlbumID = a.ID) b
Or This
SELECT a.AlbumID, a.Title, a.Date, (SELECT TOP (1) c.PhotoID FROM Photos c WHERE(c.AlbumID = a.AlbumID)) AS PhotoID FROM PhotoAlbums as a
if the tables are like:
PhotoAlbums
AlbumID PK
...
Photos
PhotoID PK
AlbumID FK
Photo
...
Here is the SQL query:
SELECT
a.*,p.Photo
FROM PhotoAlbums a
LEFT OUTER JOIN (SELECT
AlbumID,MIN(PhotoID) AS MinPhotoID
FROM Photos
GROUP BY AlbumID
) dt ON a.AlbumID=dt.AlbumID
LEFT OUTER JOIN Photos p ON dt.MinPhotoID=p.PhotoID
working example:
DECLARE @PhotoAlbums table (AlbumID int)
INSERT @PhotoAlbums VALUES (1)
INSERT @PhotoAlbums VALUES (2)
INSERT @PhotoAlbums VALUES (3)
DECLARE @Photos table (PhotoID int,AlbumID int, Photo varchar(10))
INSERT @Photos VALUES (1,1,'A')
INSERT @Photos VALUES (2,1,'B')
INSERT @Photos VALUES (3,1,'C')
INSERT @Photos VALUES (4,2,'AA')
INSERT @Photos VALUES (5,3,'AAA')
INSERT @Photos VALUES (6,3,'BBB')
SELECT
a.*,p.Photo
FROM @PhotoAlbums a
LEFT OUTER JOIN (SELECT
AlbumID,MIN(PhotoID) AS MinPhotoID
FROM @Photos
GROUP BY AlbumID
) dt ON a.AlbumID=dt.AlbumID
LEFT OUTER JOIN @Photos p ON dt.MinPhotoID=p.PhotoID
OUTPUT:
AlbumID Photo
----------- ----------
1 A
2 AA
3 AAA
(3 row(s) affected)
The Linq query could be something like this:
from album in context.PhotoAlbums
from photo in album.Photos.Take(1).DefaultIfEmpty()
select new
{
Album = album,
Photo = photo
}
精彩评论