2

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'
GMB
  • 216,147
  • 25
  • 84
  • 135
JohnnyRingo506
  • 25
  • 6

1 Answers1

3

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'
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Checked using my own db and verified that the error goes away when you change to alias. This is correct – user8735467239 Mar 13 '19 at 20:48
  • I did, unfortunately I haven't reached awesome status for you to see it. – JohnnyRingo506 Mar 14 '19 at 17:33
  • @JohnnyRingo506: you can *accept* the answer even without reputation (what you cannot do is *upvote* the answer). To accept, you need to check the green check sign, as explained in the link I provided in my last comment – GMB Mar 14 '19 at 18:14