erewash

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

Thank you for your reply - yes, I was uneasy about the way this is put together - I'm sure splitting the data would solve all sorts of things, but the way that the client wants to be able to run searches on the database made me build it that way, plus the need to make all the drop-down selects etc. manageable for me.... I think that I will have to do just as you say in the end - it would also avoid the trauma of my other question on the board at the minute. But it would be interesting to know if there is a way of combining the syntax in this way. [!--quoteo(post=380062:date=Jun 4 2006, 06:02 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 4 2006, 06:02 PM) [snapback]380062[/snapback][/div][div class=\'quotemain\'][!--quotec--] Your code structure combining eventtype_location seems to be you main problem. Why not have different columns and make life easier for yourself? WHERE type IN ('wshop', 'seminar') OR (type = 'lect' AND location <> 'london) [/quote]

Having spent the whole day reading up about database coding - is it possible with MySQL to combine the 'LIKE' and the 'IN' statements in a selection? This would get me part way to where I am wanting to be, but I can't find any references to syntax like this. So if my event codes have a first part to express type of event, and a second part to show specific event, my [u]fake code[/u] needs to run something like:[code]WHERE pe.people_id <> '' AND lk.code_id IN ( (LIKE 'wshop_%'), (LIKE 'seminar_%'), (LIKE 'lect_%' ) ) AND lk.code_id <> 'lect_london' [/code] ... and this might get me 'everyone who has been to any workshop or seminar or lecture, but not the London lecture' This might save a bundle of separate statements, if it is possible to do something of this kind! Thank you....

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]

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]

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

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

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]

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!

Okay, not meaning to waste peoples' time - I find that ORDER BY at the end of the select seems to do the job for me now - had been putting it in the wrong place! A case of tying myself in knots... but is there a neater way to achieve what I am after? Thank you!

I'm sure this is really basic and it is just a matter of getting the JOIN right, but my brain is addled by trying to work out the logic; I need to run a selection, and read data into an array, from two simple tables, plus one lookup table. So, each 'people' record will have one or more addresses, and each record _may_ have one or many 'prog' records. [code] $sql = "SELECT pe.people_id, pe.loginname, pe.password, ..... ,                     addr.institution, addr.department, ..... ,                     lk.pprog_id             FROM (my_people AS pe LEFT JOIN my_address AS addr ON pe.people_id=addr.people_id)                 LEFT JOIN my_people_prog AS lk ON pe.people_id=lk.ppeople_id                   WHERE surname <> '' ";                                       [plus various other selection criteria posted from form] [/code] The first part of the selection - against people and address - works fine, but introducing the 'prog' gives me one row for each 'prog' it finds that matches, which is embarrassing.... could someone please advise?? Next scary step will be to enable the selection on the basis of the descriptor, rather than the code, for the 'prog' record - I guess this is best achieved through clever use of drop-downs on the selection form.... Thank you very much.