Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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