Badly constructed join

App Building
1

My sql will not return all rows from the xclient table. I have a very vague idea of why this might be so but have no idea how to rewrite my query. Any help would be greatly appreciated.

SELECT 
  xclient.cid, 
  xclient.name, 
  xclient.postcode, 
  xclient.territory, 
  xclient.archivedflag, 
  xclient.recruitmentstatus, 
  xclient.trainingstatus, 
  xclienttask.due AS taskdue, 
  xclienttask.priority, 
  xclienttask.name AS taskname, 
  xclienttask.description AS taskdescription, 
  xclienttask.assignee AS taskassignee, 
  xclienttask.completed, 
  xclienttask.tid 
FROM 
  xclient 
  LEFT JOIN xclienttask ON xclient.cid = xclienttask.clientid 
WHERE 
  xclient.name ILIKE {{ '%' + textInput1.value + '%' }} 
  AND xclient.territory ILIKE {{ '%' + textInput4.value + '%' }} 
  AND xclient.postcode ILIKE {{ '%' + textInput12.value + '%' }} 
  AND xclienttask.assignee ILIKE {{ '%' + textInput13.value + '%' }} 
  AND (
    CASE WHEN {{switch14.value}} = FALSE THEN xclient.archivedflag IS NOT TRUE ELSE 1 = 1 END
  ) 
  AND (
    CASE WHEN {{switch15.value}} = FALSE THEN xclienttask.completed IS NOT TRUE ELSE 1 = 1 END
  ) 
ORDER BY 
  name;
2

Hi @Adam_Thomas,

Try this for the ILIKEs

WHERE 
  (
    {{ !textInput1.value }}
    OR xclient.name ILIKE {{ '%' + textInput1.value + '%' }} 
  )
  ...

And you can simplify the switches to something like:

...
AND (
  {{ switch14.value }} 
  OR xclient.archivedflag IS NOT TRUE
)
...

Let me know if that works.

3

Many thanks for the tidy up, but I'm still not seeing all the records from xclient.

4

Query now looks like this:

SELECT

xclient.cid,
xclient.name,
xclient.postcode,
xclient.territory,
xclient.archivedflag,
xclient.recruitmentstatus,
xclient.trainingstatus,
xclienttask.due as taskdue,
xclienttask.priority,
xclienttask.name as taskname,
xclienttask.description as taskdescription,
xclienttask.assignee as taskassignee,
xclienttask.completed,
xclienttask.tid

FROM
xclient

LEFT JOIN
xclienttask
ON xclient.cid = xclienttask.clientid

WHERE
( {{!textInput1.value}} or xclient.name ILIKE {{ '%' + textInput1.value + '%' }})

AND
({{!textInput4.value}} or xclient.territory ILIKE {{ '%' + textInput4.value + '%' }})

AND
({{!textInput12.value}} or xclient.postcode ILIKE {{ '%' + textInput12.value + '%' }})

AND
({{!textInput13.value}} or xclienttask.assignee ILIKE {{ '%' + textInput13.value + '%' }})

AND ({{switch14.value}}
OR xclient.archivedflag IS NOT TRUE)

AND ({{switch15.value}}
OR xclienttask.completed IS NOT TRUE)
;

5

Solved it. I didn't realise that the 'join on' clause could contain multiple conditions (live and learn).

Final iteration of query:

SELECT

xclient.cid,
xclient.name,
xclient.postcode,
xclient.territory,
xclient.archivedflag,
xclient.recruitmentstatus,
xclient.trainingstatus,
xclienttask.due as taskdue,
xclienttask.priority,
xclienttask.name as taskname,
xclienttask.description as taskdescription,
xclienttask.assignee as taskassignee,
xclienttask.completed,
xclienttask.tid

FROM
xclient

LEFT JOIN
xclienttask
ON xclient.cid = xclienttask.clientid

AND
({{!textInput13.value}} or xclienttask.assignee ILIKE {{ '%' + textInput13.value + '%' }})

AND ({{switch15.value}}
OR xclienttask.completed IS NOT TRUE)

WHERE
( {{!textInput1.value}} or xclient.name ILIKE {{ '%' + textInput1.value + '%' }})

AND
({{!textInput4.value}} or xclient.territory ILIKE {{ '%' + textInput4.value + '%' }})

AND
({{!textInput12.value}} or xclient.postcode ILIKE {{ '%' + textInput12.value + '%' }})

AND ({{switch14.value}}
OR xclient.archivedflag IS NOT TRUE)

;

2 Likes
6

Nice, glad it worked out

1 Like
7