I have a people table 'ocp_people', and an address table 'ocp_address', and a table containing lots of 'event participation' codes 'ocp_people_prog'. Each individual should have one address (but not always), and may have lots of event codes.
As modified by Barand's answer it reads something like this;
SELECT DISTINCT pe.people_id, pe.surname, pe.forename FROM (ocp_people AS pe LEFT JOIN ocp_address AS addr ON pe.people_id=addr.people_id) LEFT JOIN ocp_people_prog AS lk ON pe.people_id = lk.ppeople_id WHERE pe.people_id <> '' AND ( (lk.pprog_id LIKE 'code1%') OR (lk.pprog_id LIKE 'code2%' ) ) GROUP BY pe.people_id ORDER BY pe.surname(I have to generate the boolean operators with a drop-down for the user, which is fiddly but works)
This is fine where I want everyone who fits into either category; but how about if I want to collect only those where both criteria apply - and I might be wanting to search more than two codes as here.
Thus in pseudocode;
AND lk.pprog_id LIKE 'code1%' AND lk.pprog_id LIKE 'code2%' AND lk.pprog_id NOT LIKE 'code3%'
How is it possible to achieve this within a single statement? I can't see any way of achieving the logic of this statement without applying multiple 'AND's. Any assistance gratefully received.....