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.

closed as unclear what you're asking by ypercubeᵀᴹ, Philᵀᴹ, Tom V, mustaccio, Shanky Aug 16 '16 at 12:13

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
  • 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

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