For use on blogpost https://normansolutions.co.uk/post/how-to-get-a-latest-student-photo-from-isams
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' ) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment