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.
-
Which photo from each album do you want?Marcelo Cantos– Marcelo Cantos2010-03-31 10:50:18 +00:00Commented Mar 31, 2010 at 10:50
-
for now any photo.. later I will edit table Photos so that it has field "albumcover", but now I need random oneilija veselica– ilija veselica2010-03-31 10:51:42 +00:00Commented Mar 31, 2010 at 10:51
-
your selected query is inefficient, as it must run a different query to find the photo for each row returned from PhotoAlbums. You would be much better off using a derived table and joining PhotoAlbums to that.KM.– KM.2010-03-31 12:55:50 +00:00Commented Mar 31, 2010 at 12:55
-
I may agree with KM sub-queries are not recommended in the application because it reduces performance while fetching the data. in this scenario the better way is to create a new column in "PhotoAlbums" Table named "DefaultPhoto" and put the default photo id in it, which will make a straight forward select query with out any join which will enhance the performance and maintainability.Kronass– Kronass2010-04-01 06:55:46 +00:00Commented Apr 1, 2010 at 6:55
-
I agree with you, that is definitively less painful solution.ilija veselica– ilija veselica2010-04-01 09:34:01 +00:00Commented Apr 1, 2010 at 9:34
Add a comment
|
3 Answers
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
Comments
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)
7 Comments
ilija veselica
hmmm... When I try to execute your and Kronass's query, VS2008 formats the query and then returns error. Is SQL the same for MySQL and MSSQL?
ilija veselica
And yes, the tables are like you assumed
ilija veselica
If I click Execute, error is following: "Error in GROUP BY clause. Unable to parse query text." If I click Verify Syntax, error is: "Incorrect syntax near 'a' "
KM.
See latest edit, I missed the
ON here: ) dt ON a.AlbumID=, I also added a working example.ilija veselica
It's working now, thanks a lot. It's a bit complex to understand for me... What does 'dt' stand for? I suppose it stands for Photos table but I'm used to make alias of expression with 'AS'.
|
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
}
