For use on blogpost https://normansolutions.co.uk/post/how-to-get-a-latest-student-photo-from-isams

iSAMSFamilyList.sql

SELECT p.txtschoolid, 
       p.txtsurname, 
       p.txtforename, 
       A.txtcontactssurname, 
       A.txtcontactsforename, 
       AL.tblpupilmanagementaddresslinkid, 
       A.tblpupilmanagementaddressesid, 
       ( '(' + Isnull(A.txtrelationtype, '') ) + ')' + Char(13) + Char(10) + 
       ( Isnull(A.txtcontactstitle, '') + ' ' 
         + Isnull(A.txtcontactsforename, '') + ' ' 
         + Isnull(A.txtcontactssurname, '') ) + Char(13) + Char(10) + ( 
       Isnull(A.txtsecondarytitle, '') + ' ' 
       + Isnull(A.txtsecondaryforename, '') + ' ' 
       + Isnull(A.txtsecondarysurname, '') ) AS ParentNames, 
       ( p.txtprename + ' ' + p.txtsurname ) AS PupilName, 
       p.txttype, 
       y.txtyearcode, 
       'https://YOURSCHOOLNAME.isams.co.uk' + pic.txtpath AS Photo 
FROM   tblpupilmanagementaddresslink AL 
       INNER JOIN tblpupilmanagementpupils p 
               ON( AL.txtschoolid = p.txtschoolid ) 
       INNER JOIN tblpupilmanagementaddresses A 
               ON( AL.intaddressid = A.tblpupilmanagementaddressesid ) 
       INNER JOIN tblschoolmanagementyears y 
               ON( p.intncyear = y.intncyear ) 
			   
--THIS IS THE SECTION THAT ESTABLISHES THE LATEST STUDENT PHOTO   
       JOIN tblpupilmanagementpictures pic 
         ON( p.txtschoolid = pic.txtschoolid ) 
			LEFT OUTER JOIN tblpupilmanagementpictures pic2 
                    ON( p.txtschoolid = pic2.txtschoolid ) 
                      AND ( pic.intorder < pic2.intorder ) 
		WHERE  pic2.txtschoolid IS NULL 
--END OF SECTION	

       AND p.intsystemstatus = '1' 
       AND a.txtrelationtype IN( 'Parents', 'Mother', 'Father' )