SELECT * FROM Tran_LibraryBooksTrans LBT
LEFT JOIN Tran_LibraryIssuedBooks LIB ON
CASE WHEN LBT.IssuedTo='SN' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
WHEN LBT.IssuedTo='SM' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
WHEN LBT.IssuedTo='BO' AND LBT.LIBRARYTRANSID=LIB.LIBRARYTRANSID THEN 1
ELSE 0 END