[SOLVED] PostgreSQL: Query with additional column based on another table

PostgreSQL: Query with additional column based on another table

Having two tables "User" and "Adress", my first query provide a unique list of the users

SELECT DISTINCT "User" FROM "Scheme."T User" ORDER BY "User" ASC;

The second table “Address” has the text fields "Name1" and "Range" among others.

Now I need an additional column "^Adress" in the first query, which analyzes whether "User" exists as a substring of the "Name1" field under the "Range" condition; my approach

SELECT DISTINCT "User",
   IF 
      EXISTS (SELECT "Name1" FROM "Scheme"."T Adress" WHERE "Range" = 'XXX' AND "Name1" LIKE '%' & "User" & '%')
         THEN 'yes'
         ELSE 'NO'
      END IF AS "^Adress"
FROM "Scheme"."T User"
ORDER BY "User" ASC
;

provides SyntaxError. How to achieve my goal? Thank you!

Solution

There's no if in plain SQL in Postgres. Use a case instead. String concatenation is done with the || operator, not &.

SELECT DISTINCT "User"
     , CASE WHEN EXISTS 
         (SELECT FROM "Scheme"."T Adress" 
          WHERE "Range" = 'XXX' 
            AND "Name1" LIKE '%' || "User" || '%')
         THEN 'yes'
         ELSE 'NO'
      END AS "^Address"
FROM "Scheme"."T User"
ORDER BY "User" ASC;

There's no need to select any specific column inside the exists() - it only checks if the sub-expression returns any rows but it discards all columns/fields in them.