Reductive selects - applying AND to a single field.

[derwent]

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. The code at the moment looks like this;

SELECT lk.ppeople_id, lk.pprog_id, pe.surname, pe.citation_form 
FROM ocp_people_prog AS lk, 
(ocp_people AS pe LEFT JOIN ocp_address AS addr ON pe.people_id=addr.people_id) 
WHERE lk.ppeople_id <> '' 
AND (lk.pprog_id LIKE 'event1%' 
OR lk.pprog_id LIKE 'event2%' 
OR lk.pprog_id LIKE 'event3%' 
OR lk.pprog_id LIKE 'event4%' ) 
AND pe.people_id=lk.ppeople_id 
GROUP BY pe.people_id ORDER BY pe.surname

This is fine where I want everyone who fits into any category; but how about if I want to collect only those where all criteria apply, or even apply a NOT match?

===================
bloke1 has been to event1, event2, event3
bloke2 has been to event2
bloke3 has been to event1
bloke4 has been to event2, event3

who has been to event1?

= bloke1, bloke3

who has been to event2?

= bloke1, bloke2, bloke4

who has been to event1 AND event2?

= bloke1

who has been to event2 BUT NOT event1?

= bloke2, bloke4

Thus in pseudocode;
CODE
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, which always give me a '0' selection. Any assistance gratefully received.....

[frikikip]

Can't you use the wildcard in front of the event code like:

AND (lk.pprog_id LIKE '%event1%'
OR lk.pprog_id LIKE '%event2%'
OR lk.pprog_id LIKE '%event3%'
OR lk.pprog_id LIKE '%event4%' )
AND lk.pprog_id NOT LIKE '%event5%'

Something like this should work I think.

Greets

[derwent]

Thanks for the response! I'm staring at this code thinking 'there must be tens of thousands of sites that run this kind of database searching, and must do it more neatly than this'.

The code is like this at the moment - doing a couple of self-joins to enable multiple searches. There must be a better way than what you see here ...

SELECT lk.ppeople_id, lk.pprog_id, ll.ppeople_id, ll.pprog_id, lm.ppeople_id, lm.pprog_id, 
pe.surname, pe.citation_form 
FROM ocp_people_prog AS lk, ocp_people_prog AS ll, ocp_people_prog AS lm, (ocp_people AS pe 
LEFT JOIN ocp_address AS addr ON pe.people_id=addr.people_id) 
WHERE lk.ppeople_id <> '' 
AND ll.ppeople_id = lk.ppeople_id 
AND lm.ppeople_id = lk.ppeople_id 

AND (lk.pprog_id LIKE 'event1%'    // these ones harvest all the
OR lk.pprog_id LIKE 'event2%' )     // people with 1 or 2...

AND (ll.pprog_id LIKE 'event3%' )             // these ones reduce
AND (lm.pprog_id LIKE 'event4%' )          // the selection - only those with 3 and 4

AND pe.people_id=lk.ppeople_id 
GROUP BY pe.people_id ORDER BY pe.surname