Remove rows during a join based on values in other rows using Snowflake SQL-CodePudding

I am joining multiple tables together and getting a result similar to

USER_ID   YEAR    FIELD1   FIELD2  ...
1         2020    --      X
1         2020    --      Y
1         2020    --      Z
1         2020    Value   X
1         2020    Value   Y
1         2020    Value   Z
1         2021    --      X
1         2021    --      Y
1         2021    --      Z
2         2020    --      X
2         2020    --      Y
2         2020    --      Z
2         2020    Value   X
2         2020    Value   Y
2         2020    Value   Z
3         2020    --      X
3         2020    --      Y
3         2020    --      Z

I want to remove the rows where FIELD1 is '--' if there are values for the same USER_ID and YEAR where FIELD1 is not '--'. So I should end up with something like this:

USER_ID   YEAR    FIELD1   FIELD2  ...
1         2020    Value   X
1         2020    Value   Y
1         2020    Value   Z
1         2021    --      X
1         2021    --      Y
1         2021    --      Z
2         2020    Value   X
2         2020    Value   Y
2         2020    Value   Z
3         2020    --      X
3         2020    --      Y
3         2020    --      Z

I do not want to delete the rows from the table where USER_ID, YEAR, and FIELD1 come from. I just want to add the proper where clause to the end of a long join query. I currently have something like this at the end of my join query which works without the where clause:

select...
from TABLE1 as t
join...
where t.FIELD1 = '--' and exists(
   select *
   from TABLE1 as t2
      where t.USER_ID= t2.USER_ID and
      t.YEAR = t2.YEAR and
      t2.FIELD1 != '--')

CodePudding user response：

You can OR` as it is mutily exclusive to the other condition

SELECT * FROM table1 t1
WHERE (FIELD1 = '--' 
AND NOT EXISTS( SELECT 1 FROM table1 t2 WHERE t1.USER_ID =  t2.USER_ID AND  t1.YEAR = t2.YEAR
AND t2.FIELD1 <> '--'))
OR  FIELD1 <> '--' 
ORDER BY USER_ID, YEAR