Querying MultiSelect Dropdown not returning data if no values selected

I'm Following the documentation steps for the multiselect dropdown but query returns no data if no filter selected and ANY() clause returns [] (empty list) not returning true or false as expected.

It's Works fine with postgreSql Databases but not working with mysql

1 Like

Hi, @OmarShendy If I am not wrong, you are looking for help to add filters in your query. I think this link will help you out.

The problem is when you're dealing with multiselect dropdown and want to return all data {{!select1.value}} not returning true as select1.value if no value selected evaluates to empty string so there's something wrong or something i'm missing in the logic

Hi Omar! Ah, it looks like MySQL is a bit trickier than our docs go into (I'll add a section clarifying!). I played around with this for a little while, and finally got it to work. It wasn't working (and was erroring out) because even though the first statement after the WHERE was returning true (which means the query should just stop the WHERE condition and return everything), the second (IN) statement was still running. This confused the query because it was then looking for IN ([]), which causes an error. IN (['']) works, so I added a condition to return [''] if the multiselect is empty.
Let me know if you have any questions :slight_smile:

select *from peoplewhere ( ({{!multiselect1.value.length ? 1 : 0}} = 1)or id IN ({{ multiselect1.value.length == 0 ? [''] : multiselect1.value }}) )









2 Likes

I am curious how this might be applied to MS SQL. I can get no selections to load the entire data set and I can get one selection to load a data set filtered. However, when I select 2 or more the data set returns no results. I cannot tell if it is doing an "and" filter or if I'm doing something else wrong.

For Example, if I wanted all states returned and I select no states I get the full set. If I just want NY returned I can select NY and get a filtered data set to just NY. However, if want NY and MA I cannot get that returned.

Hi @tcobrock! Happy to help with this. Would you mind sharing a screenshot of your current query?

These docs might also be helpful! https://docs.retool.com/docs/sql-query-faq#how-do-i-show-all-my-data-when-a-filter-is-not-in-use