Jump to content

Archived

This topic is now archived and is closed to further replies.

erewash

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

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!

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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....

Share this post


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

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites

×

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.