Imaš tu jedan join viška. U suštini, treba ti ovo (pretpostavljam):
Kôd:
SELECT *
FROM resources r
LEFT JOIN resource_attributes a_6 ON r.id = r.resource_id AND a_6.attribute_id = 6
LEFT JOIN resource_attributes a_8 ON r.id = r.resource_id AND a_8.attribute_id = 8
WHERE
a_6.attribute_value LIKE 'La%'
AND a_8.attribute_value LIKE 'Ho%'
Nema potrebe za GROUP BY.