Custom Query Different Results

Wappler General
#1

I have a Server Action that has a Custom Query and when I test the query with a “Test Value” it only returns one result. But if I type the value directly into the SQL statement and run, it returns the correct three results. What would cause this to happen?

SQL Statement -
SELECT app_settings.app_name, app_settings.app_link
FROM app_settings
WHERE app_settings.menu_id = :P1
AND app_settings.app_id IN (:P2)
AND app_settings.app_status = 1

:P1 = 1
:P2 = 1,2,3,4,5,6,7,8

SQL Statement without P2
SELECT app_settings.app_name, app_settings.app_link
FROM app_settings
WHERE app_settings.menu_id = :P1
AND app_settings.app_id IN (1,2,3,4,5,6,7,8)
AND app_settings.app_status = 1

#2

This might help.

#3

Thanks @TomD! With that and a little Google help was able to rewrite the entire query.

SELECT DISTINCT app_settings.app_name, app_settings.app_link
FROM app_access2
INNER JOIN app_settings ON FIND_IN_SET(app_settings.app_id, app_access2.app_array) !=0
WHERE app_settings.menu_id = :P1
AND app_settings.app_status = 1

This is Solved!

1 Like