Jump to content

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


erewash

Recommended Posts

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!
Link to comment
Share on other sites

[!--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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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....
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.