Q:

How to write query in 1 to many relation, to get random 1 record from 2nd table in sql query

I have 3 tables
tblAlbum
------------------------
AlbumID    Int
Name  nvarchar(255)

tblPhotos
-------------------------
PhotoID          int
FileName        nvarchar(255)

tblAlbumPhotos
------------------------
AlbumPhotoID (P)    int
AlbumID             int
PhotoID             int

This is the query to get the result from Album and 1 photo
    with Album(RankNo,AlbumID, Name, FileName, PhotoID) as
(
select 
rank() over(partition by GA.AlbumID order by GP.photoid) as RankNo ,
GA.AlbumID,GA.Name, GP.FileName, GP.PhotoID, GP.CreatedDate from tblAlbum GA
inner join tblAlbumPhotos GAP on GAP.AlbumID=GA.AlbumID
inner join tblPhotos GP on GP.PhotoID=GAP.PhotoID
)
select RankNo,AlbumID, Name, FileName, PhotoID from Album where RankNo=1

BUT, I Want a result from tblAlbum table and FileName any 1 (Random) from tblPhotos

A:

Try this (I've only changed your order by clause):
;with Album(RankNo,AlbumID, Name, FileName, PhotoID) as
(
select 
rank() over(partition by GA.AlbumID order by newId()) as RankNo ,
GA.AlbumID,GA.Name, GP.FileName, GP.PhotoID, GP.CreatedDate from tblAlbum GA
inner join tblAlbumPhotos GAP on GAP.AlbumID=GA.AlbumID
inner join tblPhotos GP on GP.PhotoID=GAP.PhotoID
)
select RankNo,AlbumID, Name, FileName, PhotoID from Album where RankNo=1

