0

I have two tables, linked through Artists Firstnames and surnames. One table has the artists BIOG (only one result per Artist.), the other table has the Artist's images (a lot of images per artist). I want to run a DISTINCT query showing the Artist's Image AND Biog. I am getting plenty of records with the code below, but cannot get the DISTINCT to filter out duplicates. I want the DISTINCT to work only on the Artist firstname and surname, but for it to disregard the images, which are all distinct.

Here is the code:

SELECT ArchiveCatalogues.ARTIST,
   ArchiveCatalogues.IMAGE, 
   ArchiveCatalogues.FIRSTNAME, 
   ArchiveCatalogues.SUFFIX,
   ArchiveCatalogues.TITLE,
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
FROM ArchiveCatalogues 
INNER JOIN artist
ON (ArchiveCatalogues.ARTIST = artist.surname)
AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)

Any image would be acceptable at this stage. I was thinking that the main query should be the one that queries the table with the BIOGS, since each artist is distinct on this table (no artist is mentioned more than once.) Then, all I need to do is pull the image in from the other table.

  • Not quite sure what you are wanting to see. Could you give an example of the output you are hoping for? It almost sounds like you want the artist info one time and then every image that i associated. If that is the case you are either looking at a PIVOT or two separate queries. – Steve Mangiameli Aug 15 '16 at 16:40
1

this will give image only

SELECT 
   max(ArchiveCatalogues.IMAGE),    
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
FROM ArchiveCatalogues 
INNER JOIN artist
 ON (ArchiveCatalogues.ARTIST    = artist.surname)
AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames) 
group by 
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
| improve this answer | |
  • It works on my local server only! I am now getting this error online : : : error '80040e14' The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. : : : I tried the following change : : : SELECT CAST([ArchiveCatalogues.IMAGE] AS VARBINARY(MAX)) AS ArchiveCatalogues.IMAGE, etc... : : : but to no avail. Any ideas. – HonestCon Aug 16 '16 at 12:31
  • You did not answer the question on does is support row_number(). If so use row_number() but on a different field like title – paparazzo Aug 16 '16 at 12:43
0

I am not sure do you have one artist, then you can just LIMIT your query, but if nothing you can try GROUP_CONCAT and that will put all images for that artist in same row, or you can create select in select, something like this , (SELECT archivecatalogues.image FROM archuvecatalogues Where archivecatalogues.artist = artist.surname LIMIT 1) as img,

Hope this helps

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.