PostgreSQL missing FROM-CLAUSE entry

I have the following query:

WITH sb AS (
    SELECT "shiftId", "sb.bookings"."state", "visibleAt" FROM bookings
    JOIN "memberConnections" ON "bookings"."memberId" = "memberConnections"."memberId"
    WHERE "shiftId"= 1714
)

SELECT
    COALESCE((SELECT COUNT(*) from sb where "bookings.state"='MATCHED'), 0) as matched FROM SB

It tells me: ERROR: missing FROM-clause entry for table "sb.bookings" LINE 2: SELECT "shiftId", "sb.bookings"."state", "visibleAt" FRO...

I wrote it as "sb.bookings"."state" because state is in both tables, but it didn’t want to allow me to write it as "bookings"."state" ( I don’t know why either )

Does anyone have any ideea about this?

Answer

Your CTE is prefacing the state column with an identifier which does not exist. And, you can simplify your final query:

WITH sb AS (
    SELECT shiftId, b.state, visibleAt
    FROM bookings b
    INNER JOIN memberConnections m
        ON b.memberId = m.memberId
    WHERE shiftId = 1714
)

SELECT
    COALESCE(COUNT(*), 0) AS matched
FROM sb
WHERE
    state = 'MATCHED';

The quoted version of your CTE:

WITH sb AS (
    SELECT "shiftId", b."state", "visibleAt"
    FROM "bookings" b
    INNER JOIN "memberConnections" m
        ON b."memberId" = m."memberId"
    WHERE "shiftId" = 1714
)