Help with IF in Where clause used to populate table

Queries and Resources
,
1

Hi

As a new beginner to retool, SQL and Javascript and I need some help with a MYSQL query that I'm using to populate my table.

I want to list all SubTask on user or all SubTask for a selected task based on Tab selection.

tabbedContainer2 (
TAB 1 = List all Task, user can select a task and then see all sub tasks
TAB 2 = User can see all sub tasks assaged to user
[
tabs1 used to select different status's
User can select

  • all open sub task
  • all returned sub task
  • all finish sub tasks
  • All subtasks
    ]
    )
    Note One Task can have 100 sub tasks

MySQL query:
SELECT *

FROM tblSubTasks
INNER JOIN tblTask ON tblTask.TaskID = tblSubTasks.TaskID

-I need help with creating a WHERE clause with different IF / ELSE depending on tabbedContainer2 and tabs1 selected.

{{tabbedContainer2.currentViewKey === 1 ?
WHERE tblSubTasks.taskID = list_task.selectedRows[i].taskId :

tabbedContainer2.currentViewKey === 2 ?
{{
tab4.value === 1 ?
WHERE tbltask.StatusID = 1
AND tblsubtask.StatusID = 1
AND tblsubtask.userID === return localStorage.values.MyUserID
: tab4.value === 2 ?
WHERE tbltask.StatusID = 1
AND tblsubtask.StatusID = 2
AND tblsubtask.userID === return localStorage.values.MyUserID
: tab4.value === 3 ?
WHERE tbltask.StatusID = 1
AND tblsubtask.StatusID = 2
AND tblsubtask.userID === return localStorage.values.MyUserID
:
WHERE tblsubtask.userID === return localStorage.values.MyUserID
}}

2

Where is the i coming from? Is this SQL query standalone, or are you looping through the selectedRows somehow?

That aside, one approach is to combine the conditions and setup a series of OR statements:

WHERE
     -- if currentViewKey is not 1, following set of criteria will fail
     (    {{tabbedContainer2.currentViewKey === 1}}                  
      AND tblSubTasks.taskID = {{list_task.selectedRows[i].taskId}} )
  OR 
     -- if currentViewKey is not 2, following set of criteria will fail
     (    {{tabbedContainer2.currentViewKey === 2}}
          -- You have this criteria in all events
      AND tblTask.StatusID = 1
      AND tblSubTasks.userID = {{localStorage.values.MyUserID}}
          -- your additional logic here
      AND tblSubTasks.StatusID = {{tab4.value === 1 ? 1 : 2}} )

Another approach would be a (series of) CASE statement(s)

3

Hi @jg80

It worked with you example. Thank you for your help

Yes i have a task list (table) with multiple rows selection.

1 Like