**SOLVED** logic of many-to-ones

[erewash]

I'd really appreciate help on this one.. the logic is frying my brain.

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:

[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%'
AND lk.pprog_id LIKE 'code2%'
GROUP BY pe.people_id ORDER BY pe.surname [/code]

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!

[Barand]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]WHERE pe.people_id <> ''
AND lk.pprog_id LIKE 'code1%'
AND lk.pprog_id LIKE 'code2%' [/quote]

If pprog_id begins with "code1" then it cannot also begin with "code2". The two are mutually exclusive so

[code]pprog_id LIKE 'code1%' AND pprog_id LIKE 'code2%'[/code]
can never find a match.

Do you mean

[code]WHERE pe.people_id <> ''
AND ( (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) )[/code]

[erewash]

That's exactly what I mean! I have been fiddling around with this for days; many thanks for fixing my brackets and putting me right!

NWD

========
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
Do you mean

[code]WHERE pe.people_id <> ''
AND ( (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) )[/code]
[/quote]

[erewash]

Maybe things aren't quite that simple though - I need to do multiple selections, which may be complex; eg. in pseudo;

Select person records where event codes are found, matching 'code1' AND 'code2' BUT NOT 'code3'

(I have to generate the boolean operators with a drop-down for the user, and turn these into 'AND's and 'OR's in the 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 multiple 'AND's - like here (recognising that this cannot work, as you say, however you apply the brackets...):

[code]
AND (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' )
AND (lk.pprog_id NOT LIKE 'code3%' )
[/code]

I can see that if you run a first selection, then somehow run an exclusion process on certain matches, it might work, but have no idea how to do that....

[Barand]

IF you have
[code]
AND ( (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) )[/code]

then you won't get any code3 items.

If you have only the three code values, then the above would be the same as
[code]
AND (lk.pprog_id NOT LIKE 'code3%')[/code]

[erewash]

Barand,
Thank you very much for your help on this - I understand this cannot work as written, I was just trying to show that my selections may need to be more complex than originally posted.

Sorry, I'm clearly just not explaining myself on this one - am trying to progress the issue on 'running selects on lookup tables' post.

-----

[!--quoteo(post=379992:date=Jun 4 2006, 01:40 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 4 2006, 01:40 PM) [snapback]379992[/snapback][/div][div class=\'quotemain\'][!--quotec--]
IF you have
[code]
AND ( (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) )[/code]

then you won't get any code3 items.

If you have only the three code values, then the above would be the same as
[code]
AND (lk.pprog_id NOT LIKE 'code3%')[/code]
[/quote]