Jump to content

combining IN and LIKE ??


erewash

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....
Link to comment
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]
Link to comment
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.
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.