Jump to content

Archived

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

erewash

running selects against lookup tables

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

Share this post


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

Share this post


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

Share this post


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

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.