Search code examples
mysqlsqldatabasequerying

Recieving an Unknown column 'users_personals.user_id' in 'where clause' on my sql query

I don't know why this is happening as they all exist:

SELECT (
    SELECT COUNT(user_id)
    FROM users u
    WHERE 
        u.id=users_personals.user_id
        AND u.status NOT IN (1,2,7)
    LIMIT 1
) as Total
FROM users_personals p 
WHERE 
    p.neighborhoods='Miami Beach'
    AND DATE_FORMAT(created_at, '%Y-%m')>'2018-03'
Solution

  • Table users_personals is aliased as p in the query.

    You probably want to change this:

    WHERE u.id=users_personals.user_id

    To:

    WHERE u.id=p.user_id

    NB: as far as concerns, your SQL could probably be simplified to avoid the need for an inline query.

    It’s not possible to be sure without seeing sample data and expected output... Here is a wild guess:

    SELECT COUNT(*) as Total
FROM users_personals p
INNER JOIN users u
    ON  u.id = p.user_id
    AND u.status NOT IN (1,2,7)
WHERE 
    p.neighborhoods = 'Miami Beach'
    p.created_at >= '2018-04-01'