combining IN and LIKE ??

[erewash]

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]

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)

[erewash]

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]

[Barand]

[!--quoteo(post=380067:date=Jun 5 2006, 12:11 AM:name=erewash)--][div class=\'quotetop\']QUOTE(erewash @ Jun 5 2006, 12:11 AM) [snapback]380067[/snapback][/div][div class=\'quotemain\'][!--quotec--]
...But it would be interesting to know if there is a way of combining the syntax in this way.
[/quote]

No, you can't combine IN and LIKE

a IN ('x', 'y', 'z')

is an alternative to

(a = 'x') OR (a = 'y') OR (a = 'z')

Where you have

id
-----------
code1xxx
code1zzz
code2yyy
code3ccc

You could use

WHERE SUBSTRING(id, 1, 5) IN ('code1', 'code2')

as an alternative to

WHERE (id LIKE 'code1%') OR (id LIKE 'code2%')

but that wouldn't work with 'lect_xxx', 'seminar_xxx', 'wshop_xxx' because of the different lengths of the first part of the code.