SELECT
ISNULL(atimo_a01.type, 'Other') AS type_job,
atimo_jobs.id,
atimo_jobs.title,
atimo_jobs.id_a01,
atimo_jobs.posting_date,
atimo_jobs.cutoff_date,
atimo_jobs.show,
atimo_jobs.location,
atimo_jobs.address,
atimo_jobs.city,
atimo_jobs.state,
atimo_jobs.zip,
atimo_jobs.description,
atimo_jobs.category,
img.text01 AS image,
loc.name AS location,
loc.address AS loc_address,
loc.city AS loc_city,
loc.state AS loc_state,
loc.zip AS loc_zip
FROM
atimo_jobs
LEFT JOIN
atimo_a01 ON atimo_a01.id = atimo_jobs.id_a01
LEFT JOIN
(SELECT id, text01 FROM atimo_a01) img ON img.id = atimo_jobs.id_a01
LEFT JOIN
(SELECT id, name, address, city, state, zip FROM atimo_a01) loc ON loc.id = atimo_jobs.id_a01
WHERE
atimo_jobs.id = ''
AND atimo_jobs.id_cia = 'ADOM2013'