Left Join If
SELECT CASE
WHEN p.uid IS NOT NULL THEN p.title
WHEN c.uid IS NOT NULL THEN c.header
END AS title
FROM qinx_collection AS qc
LEFT JOIN pages AS p
ON qc.foreign_uid = p.uid
AND qc.type = 'page'
LEFT JOIN tt_content AS c
ON qc.foreign_uid = c.uid
AND qc.type = 'content'
WHERE 1 = 1
AND (p.uid IS NOT NULL OR c.uid IS NOT NULL)
SELECT
tr.uid, tt_content.header, tr.header,
IF(tr.uid IS NOT NULL, tr.header, tt_content.header) AS AAA
FROM tt_content
LEFT JOIN tt_content AS tr
ON tr.l18n_parent = tt_content.uid
AND tr.header LIKE '%can%'
WHERE tt_content.header LIKE '%can%'
AND tt_content.sys_language_uid IN (0,-1);