SQL join statement with different variations in where clause

Description :

       Following code shows the SQL join statement along various  types of  variations in where clause
Here AMSAgreement_transaction_master, AMSAgreement, and AMSProgram are three tables.
        Here in where clause I compared the character, string and also used like with wildcard i.e. %1%.
And also used the "and" to combine multiple conditions. Here join is performed because two table contains one same column.
        AgreementNumber present in AMSAgreement table and agreement_no is present in AMSAgreement_transaction_master is unique column in bothe the table. Here agreement_no is foreign key in AMSAgreement_transaction_master which is primary key AMSAgreement table.

SQL Query:

select p.ProgramName, at.programme_code,a.AgreementNumber
from AMSAgreement_transaction_master at
join AMSAgreement a
on a.AgreementNumber = at.agreement_no
join AMSProgram p
on p.ProgramId=at.programme_code
where
at.SubtitleLanguages like '%1%'
and a.IsLegalApproved ='Y'
and at.rights_code='49'
and p.ProgramName like 'k%'