use multiple values field in query

Applications Grid / Reports
#1

Hi There,

i have a table with a field with multiple values.

{ev_organize) with multiple values like 67,73,518,618

i have an global like [usr_st_id] that refers to a user id.

i tried everything in the sql query

WHERE {ev_organize} LIKE ‘%[usr_st_id]%’ or regexp

but when the id of the user is for example 18 the user is accepted.

what do i do wrong?

#2

Hello,
Like takes into account all that is 18, so 618, 718, 18
but if we take in or not in, it’s better, because we consider in the series, or out of the series
so I would have used:
WHERE [usr_st_id] in ({ev_organize})
respecting the use of parentheses,

#3

Hi rotrax, you’re using WHERE with % around your user id.
Thus it checks for 18 somewhere in the values and finds it in 518.

Maybe you can try IN as instead of LIKE in your WHERE statement.

Sincerly
Gunter Eibl

#4

Hi Gunther,
I used the IN statement for a sollution like

WHERE [usr_st_id] IN ("{ev_organize}")

this didn’t work, gives no result. if i lose the brackets " i get an error code

If i use

WHERE ‘35’ IN ("{ev_organize}") i getno result while 35 is part of the string 1973,1934,35

…

#5

use apostrophe and not quotation mark in your request, it had to get better,
WHERE ‘35’ IN ("{ev_organize}") i getno result while 35 is part of the string 1973,1934,35
Quotation say string “{ev_organize}”
apostraphe say (1973,1934,35)
–>>> WHERE ‘35’ IN (’{ev_organize}’) i getno result while 35 is part of the string 1973,1934,35

#6

use apostrophe and not quotation mark in your request, it had to get better,

WHERE ‘35’ IN ("{ev_organize}") i getno result while 35 is part of the string 1973,1934,35

Quotation say string “{ev_organize}”

apostraphe say (1973,1934,35)

–>>> WHERE ‘35’ IN (’{ev_organize}’) i getno result while 35 is part of the string 1973,1934,35

#7

sorry double answer browser issues

#8

this should work
WHERE ‘35’ IN (’{ev_organize}’)

#9

Sorry Rotrax, but I have problems with the scriptcase advertising banner 9.4, it causes me errors in firefox, it sends before I have time to correct and adapt the text of my answer,

#10

Well… IN doesn’t do the job in the sql query with a comma separated field so i did a workaround. maybe someone else has some benefit of my… no so nice sollution.
but this works!

WHERE st_id = [usr_st_id] OR (ev_eigenaren LIKE “%,[usr_st_id]%” OR ev_eigenaren LIKE “[usr_st_id],%”)

the first like searche for the first userid in the comma separated field and the second one searched the rest of the string… :rolleyes:

#11

tried this?
WHERE ‘ev_organize’ LIKE CONCAT(’%’, [usr_st_id], ‘%’) ;
or
where Locate([usr_st_id],‘ev_organize’)!=0;

#12

How about using FIND_IN_SET()
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Sample:

mysql> SELECT FIND_IN_SET(‘b’,‘a,b,c,d’);
-> 2

Or in the where part of the SQL:

SELECT … WHERE FIND_IN_SET(field_name,‘a,b,c,d’)>0

Hope this will help in your project.

Sincerely

Gunter Eibl

#13

both thanks! i’ll give it a try