A proper way to filter for a status when the status is explicitly filtered out with a sql query-CodePudding

I have a sql query that is explicitly looking for "active" items (108) and filtering out "inactive" items (117).

Although, I have found that when I try to run this query and search for inactive items, of course, nothing comes back because we have stated WHERE COALESCE(i.item_status_id, 108) <> 117

This is the original sql:

SELECT st.scope_type,
       COALESCE(Sum(0   1), 0)
FROM   scope_inventory_view i
       INNER JOIN security y
               ON y.security_id = i.update_user
       INNER JOIN product_info vp
               ON vp.product_id = i.product_id
       INNER JOIN hl_vendorproduct hvp
               ON hvp.product_id = i.product_id
       INNER JOIN hl_scopetype st
               ON st.scope_type_id = i.scope_type_id
       INNER JOIN hl_itemstatus ist
               ON ist.item_status_id = COALESCE(i.item_status_id, 108)
       LEFT JOIN hl_owner o
              ON o.owner_id = i.owner_id
       LEFT JOIN hl_location l
              ON l.location_id = i.location_id
       LEFT JOIN hl_locationtype lt
              ON lt.location_type_id = l.location_type_id
       LEFT JOIN hl_sterilizerload sl
              ON sl.load_id = i.load_id
       LEFT JOIN hl_facility f
              ON f.facility_id = l.facility_id
       LEFT JOIN clientoption co
              ON co.parent_option_id = 271
                 AND ( co.facility_id = f.facility_id
                        OR co.facility_id IS NULL )
       LEFT JOIN clientoption clo
              ON clo.option_id = 271
                 AND ( clo.facility_id = f.facility_id
                        OR clo.facility_id IS NULL ),
       gl_client
WHERE  COALESCE(i.item_status_id, 108) <> 117
GROUP  BY st.scope_type WITH rollup

is there a better way to write this so that status 117 items are filtered out UNLESS the user requests them? If they do the sql query ends like so: (which gives no results)

WHERE  COALESCE(i.item_status_id, 108) <> 117
       AND ( ist.item_status_id IN( '117' ) )
GROUP  BY st.scope_type WITH rollup

Any help is appreciated!

CodePudding user response：

Your query has a lot of tables and joins which are not used in the select or the where conditions. Maybe you've simplified it for the question? If not eliminate the redundant tables in the join.
For your question of including or excluding 117 I suggest that you add the following column in your SELECT, and the same expression in the GROUP BY .

CASE WHEN 117 THEN '117' ELSE 'others' END AS type_group

The end using will be presented with the 2 figures for each scope_type which can be added together whether in code or by the person using the data if they wish to include type 117.