SQL Server - Select one random record not showing duplicates

I have two tables, events and photos, which relate together via the 'Event_ID' column. I wish to select ONE random photo from each event and display them.

How can I do this?

I have the following which displays all the photos which are associated. How can I limit it to one per event?

SELECT Photos.Photo_Id, Photos.Photo_Path, Photos.Event_Id, Events.Event_Title,
   Events.Event_StartDate, Events.Event_EndDate FROM Photos, Events 
WHERE 
   Photos.Event_Id = Events.Event_Id AND 
   Events.Event_E开发者_运维知识库ndDate < GETDATE() AND 
   Events.Event_EndDate IS NOT NULL AND
   Events.Event_StartDate IS NOT NULL
ORDER BY NEWID()

Thanks

Luke Stratton

You could use a cross apply to retrieve one random photo per event:

select *
from Events
cross apply (
    select top 1 *
    from Photos
    where Photos.Event_Id = Events.Event_Id
    order by newid()
) RandomPhoto
where Events.Event_EndDate < GETDATE()
and Events.Event_EndDate IS NOT NULL
and Events.Event_StartDate IS NOT NULL

Use an outer apply if you'd like to retrieve events without any photos.

Look at this xaprb article. It shows several techniques you could use. It recommends a minimal-row and self-join approach.