Jump to content


Photo

running selects against lookup tables


  • Please log in to reply
4 replies to this topic

#1 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 05:41 PM

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;

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

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


#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 June 2006 - 06:38 PM

umm.. just change the OR to an AND..?
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 04 June 2006 - 06:52 PM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
umm.. just change the OR to an AND..?
[/quote]


#4 Barand

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

Posted 05 June 2006 - 11:33 PM

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

Although with your peculiar coding structure I don't guarantee it - would be ok if you separated code parts as previously recommended
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

#5 erewash

erewash
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 06 June 2006 - 05:09 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users