Jump to content

Archived

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

erewash

combining IN and LIKE ??

Recommended Posts

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

Share this post


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

Share this post


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

Share this post


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

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.