SQL Left Join with Criteria

Query to get job title of staff or null if staff not assign a job-title:

 

SELECT     CORE_Staff.ID, CORE_Staff.JobTitleID, CORE_JobTitle.Name
FROM        CORE_Staff  LEFT JOIN CORE_JobTitle
                       ON CORE_JobTitle.ID = CORE_Staff.JobTitleID
where (CORE_JobTitle.Name='Driver') or (CORE_JobTitle.Name is null)

SQL Server : Query on date and HQL

Date format is 'YYYY-MM-DD'

e.g.

select * from orders where Date='2008-04-22'

HQL (as literal)

from Activity a where a.Date between '2008-01-01' and '2008-02-29'