Test code for so answer at https://stackoverflow.com/questions/35062703/sql-conditional-select-with-coalesce-possible-issue-with-grouping-or-table-join/

test.sql

WITH XX_SQDC_LINES_REF (Line, Name, Site) AS
(
  VALUES
    (1  , 'Table  ',1),
    (2  , 'Lamp   ',1),
    (3  , 'Screen ',2),
    (4  , 'Forcep ',2),
    (5  , 'Brush  ',2),
    (6  , 'Camera2',2),
    (7  , 'Screen2',2),
    (8  , 'Forcep2',2),
    (9  , 'Brush2 ',2),
    (10 , 'Camera2',2)
), SQDC_DEPARTMENT_DETAILS (Dept_ID, Line_ID, Facility_ID) AS
(
  VALUES
    (1 , 3  , 2),
    (1 , 4  , 2),
    (2 , 5  , 2), 
    (2 , 6  , 2),
    (3 , 7  , 2), 
    (3 , 8  , 2),
    (4 , 9  , 3), 
    (4 , 10 , 3)
), SQDC_VALUE_STREAM_DETAILS (Stream_ID, Line_ID, Facility_ID) AS
(
  VALUES
    (1 , 3  , 2),
    (1 , 4  , 2),
    (1 , 5  , 2), 
    (1 , 6  , 2),
    (2 , 7  , 2), 
    (2 , 8  , 2),
    (2 , 9  , 2), 
    (2 , 10 , 2)
), SQDC_SAFETY_MAX (Facility_ID, Line_ID, Actual_Date, Safety_Value) AS
(
  VALUES
    (1 , 1 , '31-Jan-16', 0),
    (1 , 2 , '31-Jan-16', 0),
    (2 , 3 , '31-Jan-16', 0),
    (2 , 4 , '24-Jan-16', 10),
    (2 , 5 , '24-Jan-16', 0),
    (2 , 7 , '24-Jan-16', 0),
    (2 , 9 , '24-Jan-16', 0)
), SQDC_DEPARTMENTS (ID, Name, Facility_ID, VS_ID) AS
(
  VALUES
    (1, 'Dept 1' , 2 , 1),
    (2, 'Dept 2' , 2 , 1),
    (3, 'Dept 3' , 2 , 2),
    (4, 'Dept 4' , 2 , 2)
), SQDC_VALUE_STREAMS(ID, Name, Facility_ID) AS
(
  VALUES
    (1, 'VS 1' , 2),
    (2, 'VS 2' , 2)
)        
--WITH 
, stream_query AS
(
  SELECT 1 AS PRIORITY, stream_id, null as dept_id, null as line, NAME, COLOR--, rownum rnum
  FROM (
    SELECT stream_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR
    FROM SQDC_VALUE_STREAMS VLS
    LEFT JOIN SQDC_VALUE_STREAM_DETAILS VS ON VLS.ID = VS.STREAM_ID
    LEFT JOIN SQDC_SAFETY_MAX KPI ON VS.LINE_ID=KPI.LINE_ID 
    WHERE vs.facility_id = 3
    group by name, STREAM_id
    ORDER by NAME
  ) a
), dept_query AS
(
  SELECT 2 AS PRIORITY, null as stream_id, dept_id, null as line, NAME, COLOR--, rownum rnum 
  FROM (
    SELECT dept_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM SQDC_DEPARTMENTS DPTS  
    LEFT JOIN SQDC_DEPARTMENT_DETAILS DT ON DPTS.ID = DT.Dept_ID 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON DT.LINE_ID=KPI.LINE_ID
    WHERE DT.facility_id = 3
    group by name, DEPT_id
    ORDER by NAME
  ) a
), prod_query AS
(
  SELECT 3 AS PRIORITY, null as stream_id, null as dept_id, line, NAME, COLOR--, rownum rnum
  FROM (
    SELECT line, NAME,  case when SAFETY_VALUE = 0 then 'GREEN'  when      SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM XX_SQDC_LINES_REF SLR 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON     SLR.LINE=KPI.LINE_ID 
    WHERE SITE =3
    ORDER by NAME
  ) a
),merged AS
(
  SELECT a.*, MIN(PRIORITY) OVER () AS HIGHEST 
  FROM (
    SELECT * FROM stream_query
      UNION ALL
    SELECT * FROM dept_query
      UNION ALL
    SELECT * FROM prod_query
  ) a  
)
SELECT *
FROM merged
WHERE PRIORITY = HIGHEST

Each where statement (x = 3 above) can be set to 1, 2 or 3 to see the 3 different types of results.
You have to change all 3 for this query to work as intended

I noticed that the facility_id for product lines 9 and 10 is set to 3 on the SQDC_DEPARTMENT_DETAILS table, but is 2 on all the other tables. I believe this should be the same throughout the data. Could this be impacting why it's working for you and not for me?

No Simon, I put those two lines in so I could actually test the 3rd case -- otherwise the 3rd case is empty. This is the exact code that worked for me in testing -- it is not wrong.