Jump to content

running selects against lookup tables


erewash

Recommended Posts

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;

[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%')
OR (lk.pprog_id LIKE 'code2%' ) )
GROUP BY pe.people_id ORDER BY pe.surname [/code]
(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 [u]pseudocode[/u];
[code]AND lk.pprog_id LIKE 'code1%'
AND lk.pprog_id LIKE 'code2%'
AND lk.pprog_id NOT LIKE 'code3%'[/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 applying multiple 'AND's. Any assistance gratefully received.....
Link to comment
Share on other sites

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) [snapback]379989[/snapback][/div][div class=\'quotemain\'][!--quotec--]
umm.. just change the OR to an AND..?
[/quote]
Link to comment
Share on other sites

[code]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[/code]

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