CASE does not work. Find the closest object from another table

Question

I am super fresh with sql, sqlite and coding, I need help and I would appreciate any information or if you could point me any direction that's perfect. This is my first serious time coding. I dug through countless topics here and other places.

Topic: To every object from table1 I need the closest object from table2. Then I want to classify it as phrase 'Good' (for distance 1m or closer) or 'Not good' (distance over 50m) in the new Comment field.

What am I doing wrong? Something basic maybe. I get near "CASE": syntax error

Code I did:

SELECT
    id1,
    id2,
    distance,
    UWAGI
FROM (
    SELECT
        table1.dp_oid AS id1,
        table2.gml_id AS id2,
        NULL AS Comment,
        ST_Distance(
            MakePoint(table1.x, table1.y, 2176),
            MakePoint(table2.x, table2.y, 2176)
            ) AS distance
    FROM
        table1
    LEFT JOIN
        table2 ON 1=1
        ) AS distances
WHERE 
    CASE
        WHEN distance IS NULL OR distance <=1 THEN 'Good'
        WHEN distance >50 THEN 'Not good'
        ELSE NULL
        END AS UWAGI
        
GROUP BY id1
ORDER BY distance
;

Without part below I get the result, but all rows have Comment "NULL" which I am trying to change depending on the distance:

WHERE
    CASE
        WHEN distance IS NULL OR distance <=1 THEN 'Good'
        WHEN distance >50 THEN 'Not good'
        ELSE NULL
        END AS UWAGI

I try to affect the result, but somehow I feel like maybe I use code that expects me to address a specific table to apply the code to. I expect the result to export it later as a new file.

Expected result:

| id1 | id2 | distance |  Comment |
|-----+-----+----------+----------|
|  1  | i14 |    0.5   |   Good   |
|  2  | i57 |   62.0   | Not Good |
|  3  | i09 |    0.02  |   Good   |
|  4  | i11 |  227.0   | Not good |

All done in QGIS 3.18 in DBManager on Spatialite database with imported two tables in Shapefile format.

Answer 1

I don't have your DB / Tables so I can't verify this, but try this:

SELECT
    id1,
    id2,
    distance,
    CASE
        WHEN distance IS NULL OR distance <=1 THEN 'Good'
        WHEN distance >50 THEN 'Not good'
        ELSE NULL
    END AS Comment
FROM (
    SELECT
        table1.dp_oid AS id1,
        table2.gml_id AS id2,
        NULL AS Comment,
        ST_Distance(
            MakePoint(table1.x, table1.y, 2176),
            MakePoint(table2.x, table2.y, 2176)
            ) AS distance
    FROM
        table1
    LEFT JOIN
        table2 ON 1=1
        ) AS distances            
GROUP BY id1
ORDER BY distance
;