SELECT cp.ID AS 'campaign_id', job.job_title as title, job.reference, job.expires, job.short_desc as description, job.job_location as location, job.salary_from, job.salary_to, job.salary_currency as currency, job.employment_type as type, REPLACE(ch.TINY_URL, 'http://', 'https://') as url, cp.REMOVE_SALARY as hideSalary, md.name as dept_name, CASE WHEN job.job_discipline_id IS NOT NULL AND job.job_discipline_id != '' THEN job.job_discipline_id ELSE job.discipline_id END AS discipline_id FROM TXT2GIVE_CHANNELS ch LEFT JOIN TXT2GIVE_CAMPAIGNS cp ON ch.CAMPAIGN_ID = cp.ID LEFT JOIN vid_campaign job ON ch.CAMPAIGN_ID = job.campaign_id LEFT JOIN disciplines_hotel_chocolat md ON job.discipline_id = md.id OR job.job_discipline_id = md.id WHERE cp.CHARITY_ID = :clientId AND cp.ARCHIVE = 0 AND cp.CLOSED = 0 AND ch.TYPE_ID = 71 AND ch.LAUNCHED = 1 AND cp.END_DATE >= :midnight AND cp.INTERNAL_JOB = 0 AND job.job_discipline_id < 36 GROUP BY job.campaign_id ORDER BY cp.END_DATE ASC, job.job_title ASC