sql and linq query

Question

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 LINQ version of this query. Thanks in advance.

Answer 1

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

Answer 2

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)

Answer 3

@Kronass
I'm trying to make your Query work but I'm not succeeding. I tried this:

SELECT     a.AlbumID, a.Title, a.Date, b.PhotoID
FROM         PhotoAlbums AS a CROSS JOIN
                          (SELECT     TOP (1) PhotoID, AlbumID
                            FROM          Photos
                            WHERE      (AlbumID = a.AlbumID)) AS b

But I always get following error:
The multi-part identifier "a.AlbumID" could not be bound.
Do you know what is wrong in this code?

Answer 4

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
   }