How can I select 4 distinct values from 2 tables c

问题:

I have 2 tables that Im trying to select 4 results from ordered by GalleryID DESC. The fields Im looking to return are GalleryID, GalleryTitle, GalleryDate from the Galleries table, and MediaThumb from the Media Table.

Now the catch is that the Media table has multiples of 10 of GalleryID's. So, there could be 10 rows in the Media table with the same GalleryID. All I need is one MediaThumb to go along with the galleryid.

Im not sure how to formulate a query to return 4 distinct galleryid's from the galleries table and a MediaThumb from the media table.

Essentially what im trying to do is return 4 unique photo galleries with a cover photo from the media table.

Any help would be appreciated. I provided a snapshot via query designer in sqlserver of my 2 tables.

回答1:

Something like this?

SELECT TOP 4 
    g.GalleryID
    ,g.GalleryTitle
    ,g.GalleryDate
    ,MAX(m.MediaThumb) AS MaxMediaThumb
FROM Galleries g
    INNER JOIN Media m
        ON g.GalleryID = m.GalleryID
GROUP BY g.GalleryID, g.GalleryTitle, g.GalleryDate

回答2:

What I understand

SELECT DISTINCT(g.GalleryID), g.GalleryTitle, g.GalleryDate, MAX(m.MediaThumb)
FROM Galleries g
    INNER JOIN Media m
        ON g.GalleryID = m.GalleryID
GROUP BY g.GalleryID, g.GalleryTitle, g.GalleryDate

回答3:

If I understand you correctly, for each GalleryID you want any one MediaThumb and you don't care which. If that is correct, then I suggest you SELECT max(MediaThumb) and GROUP BY GalleryID.