How do I select column from table where it's missing a field description

Question

I have 3 tables "prem_pay", "sched", "clist". My SELECT statement needs to include 3 columns which are "reason", "cl_desc"(description), and "count". However if the cl_desc field is null/empty it should display that field and still keep the reason and count in the result.

The prem_pay table has a column "reason" which should match the clist table column "cl_val". The "sched" table is only used for date matching

This is my first attempt.

SELECT c.cl_val, c.cl_desc, 
    (SELECT count(*) FROM prem_pay p, sched s
    WHERE p.shiftdate >= '05-Nov-2023' AND p.shiftdate <= '02-Dec-2023'
    AND s.cost_unit = 'TRAIN' 
    AND p.id=s.id AND p.shiftdate=s.shiftdate AND p.shiftstart=s.shiftstart
    AND SUBSTRING(c.cl_val, 0, 10) = p.reason) 
FROM clist c where cl_name = 'PREMIUM_REASON';

The query results:

|cl_val              |cl_desc                      |col3         |
+--------------------+--------------------------------------------
|AD                  |Admission                    |            0|
|COVID-19            |COVID-19                     |            0|
|DC                  |Documentation                |            0|
|EW                  |Extreme Weather              |            2|
|HC                  |High Census/Acuity           |            0|
|HOL                 |Holiday                      |            0|
|ME                  |Mandatory Education          |            0|
|OP                  |Open Positions               |            0|
|SELECT              |SELECT A REASON              |            0|
|SELECT A REASON     |SELECT A REASON              |            0|
|SN                  |Sitter Needed                |            0|
|UL                  |Unplanned Leave              |            0|
|UP                  |Unpredicted Patient Care     |            0|
+--------------------+--------------------------------------------

So from this result everything looks fine except my query didn't grab the field with missing cl_desc. I know I need to use a LEFT JOIN for this so I tried

SELECT p.reason, c.cl_desc, count(*)
FROM sched s, prem_pay p
LEFT JOIN clist c
  ON c.cl_name = 'PREMIUM_REASON' AND SUBSTRING(c.cl_val, 0, 10) = p.reason 
WHERE s.shiftdate >= '08-Oct-2023' AND s.shiftdate <= '04-Nov-2023'
AND s.cost_unit = 'TRAIN'
AND p.id=s.id AND p.shiftdate=s.shiftdate AND p.shiftstart=s.shiftstart 
GROUP BY 1,2

The query results:

|reason    |cl_desc            |col3   |
+----------+-------------------+--------
|EW        |Extreme Weather    |      2|
|LC        |                   |      1|
+----------+----------------------------

This query grabbed the missing field but now I don't have my other rows that had 0 counts. What type of JOIN do I need for this or what do I have to change in my query? Please let me know if more information is needed.

This is the expected result I want to get:

|cl_val              |cl_desc                      |col3         |
+--------------------+--------------------------------------------
|AD                  |Admission                    |            0|
|COVID-19            |COVID-19                     |            0|
|DC                  |Documentation                |            0|
|EW                  |Extreme Weather              |            2|
|HC                  |High Census/Acuity           |            0|
|HOL                 |Holiday                      |            0|
|ME                  |Mandatory Education          |            0|
|OP                  |Open Positions               |            0|
|SELECT              |SELECT A REASON              |            0|
|SELECT A REASON     |SELECT A REASON              |            0|
|SN                  |Sitter Needed                |            0|
|UL                  |Unplanned Leave              |            0|
|UP                  |Unpredicted Patient Care     |            0|
|LC                  |                             |            1|
+--------------------+--------------------------------------------

Answer 1

It is like two queries, one on prem_pay and sched, and one on clist, and you want to join these query results and keep the rows even when the other query has no match. That is a full outer join.

According to their docs (https://docs.actian.com/ingres/11.0) they support CTEs (aka WITH clauses), FULL OUTER JOIN, joining with USING, date literals and much more. So, the following query may work for you:

WITH
  p AS
  (
    SELECT
      p.reason,
      count(*) AS cnt
    FROM prem_pay p
    JOIN sched s ON s.id = p.id
                AND s.shiftdate = p.shiftdate
                AND s.shiftstart = p.shiftstart
                AND s.cost_unit = 'TRAIN'
    WHERE p.shiftdate >= DATE '2023-11-05'
      AND p.shiftdate <= DATE '2023-12-02'
    GROUP BY p.reason
  ),
  c AS
  (
    SELECT
      SUBSTRING(c.cl_val, 1, 10) AS reason,
      cl_val,
      cl_desc
    FROM clist
    WHERE cl_name = 'PREMIUM_REASON'
  )
SELECT
  COALESCE(c.cl_val, reason) AS clval,
  c.cl_desc AS cldesc,
  COALESCE(p.cnt, 0) AS col3
FROM p
FULL OUTER JOIN c USING (reason)
ORDER BY clval;