DISTINCT Query on an INNER JOIN

Question

I have two tables, one (ARTISTS) with a list of different artists, details and biographies, but no images. I have another table (ARCHIVECATALOGUES), full of auction results, which have the images I need. I want to generate a query which will give me all of the detail from the ARTISTS table, and include an image (does not matter which one) from the other table for each of the artists.

So I need a query which INNER JOINs the two tables. The query below is pulling every result from the ARCHIVESCATALOGUE table, i.e. multiple results per artist. But I only need one result per artist. I used DISTINCT on the artist.ArtistID field, but to no avail. Here is the code:

SELECT DISTINCT artist.ArtistID
    ,ArchiveCatalogues.IMAGER
    ,ArchiveCatalogues.AUCTION
    ,artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
FROM artist
INNER JOIN ArchiveCatalogues ON (ArchiveCatalogues.ARTIST = artist.surname)
    AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)
WHERE artist.surname >= H *
    AND artist.surname < I
GROUP BY artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
    ,ArchiveCatalogues.AUCTION
    ,artist.ArtistID
    ,ArchiveCatalogues.IMAGER

Perhaps I should be using OUTER JOIN?

Answer 1

First (common misunderstanding), distinct is not applied to individual columns, what you get is distinct rows. This is exactly the same as your GROUP BYdoes, so distinct is redundant.

Second, you need to determine which row among duplicates that you are interested in (or get a random one). You can use window functions to achieve this by enumerating ArchiveCatalogues per Artists:

 SELECT artist.ArtistID
    ,ArchiveCatalogues.IMAGER
    ,ArchiveCatalogues.AUCTION
    ,artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
    , row_number() over (partition by artist.ArtistID
                      -- add wanted order her as:
                      -- order by ...
                        ) as rn
FROM artist
JOIN ArchiveCatalogues 
    ON ArchiveCatalogues.ARTIST = artist.surname
   AND ArchiveCatalogues.FIRSTNAME = artist.firstnames
WHERE artist.surname >= H *
  AND artist.surname < I

Note that I removed the group by since I'm not sure what the purpose was. Now you can select the first row from there:

SELECT ArtistID
     , IMAGER
     , AUCTION
     , surname
     , firstnames
     , dates
     , honorific
     , biog
FROM (
    SELECT artist.ArtistID
        ,ArchiveCatalogues.IMAGER
        ,ArchiveCatalogues.AUCTION
        ,artist.surname
        ,artist.firstnames
        ,artist.dates
        ,artist.honorific
        ,artist.biog
        , row_number() over (partition by artist.ArtistID
                          -- add wanted order her as:
                          -- order by ...
                            ) as rn
    FROM artist
    JOIN ArchiveCatalogues 
        ON ArchiveCatalogues.ARTIST = artist.surname
       AND ArchiveCatalogues.FIRSTNAME = artist.firstnames
    WHERE artist.surname >= H *
      AND artist.surname < I
) as T
WHERE rn = 1;

The join looks strange but it's hard to tell without knowing what the tables and their key's look like.

Answer 2

Your DISTINCT is applied to ALL columns in your SELECT statement, not just the one you put it next to in your query. Your DISTINCT and GROUP BY are, at the moment, doing the same exact thing.

You group by is essentially saying:

I want one record per distinct combination of:

artist.surname
,artist.firstnames
,artist.dates
,artist.honorific
,artist.biog
,ArchiveCatalogues.AUCTION
,artist.ArtistID
,ArchiveCatalogues.IMAGER

In that specific order. You're basically creating a pseudo-key of what you want to consider a unique record.

Your query is actually pulling results from ARTIST, as it is your "primary" table, but only those records which have a match in the ArchiveCatalogues table, as it is the table you are INNER JOINing to (On Surname+FirstName).

If you are looking for records from both tables, whether or not they match the other table), you can use FULL OUTER JOIN, but I don't think that's what you need in this scenario.

Answer 3

The problem you have here is the query has no way to know which image you want so it gives you all that are relevant. You could add something distinct to the JOIN or use the GROUP function

For simplicity's sake and given that I cannot see your tables, I would assume that the ArchiveCatalogue table has some kind of unique identifier in it, so you could use that to pick out a single image per row

 SELECT artist.ArtistID
,ArchiveCatalogues.IMAGER
,ArchiveCatalogues.AUCTION
,artist.surname
,artist.firstnames
,artist.dates
,artist.honorific
,artist.biog
,MAX(ArchiveCatalogue.PRIMARYKEY)

FROM artist
INNER JOIN ArchiveCatalogues ON
ArchiveCatalogues.ARTIST = artist.surname AND 
ArchiveCatalogues.FIRSTNAME = artist.firstnames

WHERE artist.surname >= H *
AND artist.surname < I

GROUP BY  artist.ArtistID
,ArchiveCatalogues.IMAGER
,ArchiveCatalogues.AUCTION
,artist.surname
,artist.firstnames
,artist.dates
,artist.honorific
,artist.biog

The MAX(Archive.Key) just selects the largest value in the identifier column, which is likely the most recent image. I think it would be more efficient to filter at the JOIN, however I believe that this is easier to read