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.
Now, if I search against only one code it works fine; search against several, and I get a big '0' every time. I have tried the join various ways, with and without 'distinct', but this is how it shows right now:
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%' AND lk.pprog_id LIKE 'code2%' GROUP BY pe.people_id ORDER BY pe.surname
I recognise that I'm probably missing something really simple here; I think maybe the syntax where I am trying to join the address table is getting in the way of a simpler join. Please help!