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 JOIN
s 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
?