Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 02 June 2006 - 10:58 AM

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:

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

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!

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 03 June 2006 - 05:49 PM

[!--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

pprog_id LIKE 'code1%' AND pprog_id LIKE 'code2%'
can never find a match.

Do you mean

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 06:11 AM

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

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


#4 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 03:28 PM

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

AND (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) 
AND (lk.pprog_id NOT LIKE 'code3%' )

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

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 04 June 2006 - 06:40 PM

IF you have
AND ( (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) )

then you won't get any code3 items.

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 06:58 PM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
IF you have
AND ( (lk.pprog_id LIKE 'code1%')
OR (lk.pprog_id LIKE 'code2%' ) )

then you won't get any code3 items.

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users