running selects against lookup tables

[erewash]

Forgive me re-posting, but I guess nobody will look at a problem that has been marked as 'solved' - Barand gave me a very helpful answer but it does not fix the central problem so I will try to explain better.

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;

[code]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 [/code]
(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 [u]pseudocode[/u];
[code]AND lk.pprog_id LIKE 'code1%'
AND lk.pprog_id LIKE 'code2%'
AND lk.pprog_id NOT LIKE 'code3%'[/code]

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.....

[.josh]

umm.. just change the OR to an AND..?

[erewash]

No - Barand's original post to my original question demonstrated that this cannot work - the result is always an empty set, as I was finding with my code in the first place!



[!--quoteo(post=379989:date=Jun 4 2006, 01:38 PM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ Jun 4 2006, 01:38 PM) [snapback]379989[/snapback][/div][div class=\'quotemain\'][!--quotec--]
umm.. just change the OR to an AND..?
[/quote]

[Barand]

[code]SELECT pe.people_id, pe.surname, pe.forename, COUNT(DISTINCT lk.pprog_id) as codecount
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
HAVING codecount = 2
ORDER BY pe.surname[/code]

Although with your peculiar coding structure I don't guarantee it - would be ok if you separated code parts as previously recommended

[erewash]

Barand,
Many thanks for your help on this. Looks like I will have to do a major bit of restructuring.
Cannot believe how difficult it is to sort this out....