CODEWITHSUNDEEP
mysqlsqlsubquery
MR.GEWA
MR.GEWA

Reputation: 873

Sql Subselect count as where count is equal

Why I get Unknown column 'negavote' in 'where clause'

SELECT 
    my_users.name, 
    my_users.avatar, 
    my_users.surname, 
    fastmap_projects.*, 
    (SELECT COUNT(*) FROM `fastmap_rating` WHERE `fastmap_rating`.`map_id`=`fastmap_projects`.`id` AND `rate`='1') as `posivote`, 
    (SELECT COUNT(*) FROM `fastmap_rating` WHERE `fastmap_rating`.`map_id`=`fastmap_projects`.`id` AND `rate`='-1') as `negavote` 
FROM (`fastmap_projects`) 
    LEFT JOIN `my_users` ON `fastmap_projects`.`user_id`=`my_users`.`id` 
WHERE 
    `area_id` = '4' 
    AND (`negavote` BETWEEN -3 AND 3) 
    OR (`posivote` BETWEEN -3 AND 3)

Can you hint how can I change this to work?

Upvotes: 0

Views: 1251

Answers (2)

Clément Prévost
Clément Prévost

Reputation: 8610

You can't use column aliases in SQL where clause (except in PostgreSQL because that guy is smooth)

Either repeat the sub-select or wrap the query in a subquery and apply your filters on the upper query

SELECT 
    *
FROM (
  SELECT 
    my_users.NAME, 
    my_users.avatar, 
    my_users.surname, 
    fastmap_projects.*, 
    ( 
      SELECT Count(*) 
      FROM   `fastmap_rating` 
      WHERE  `fastmap_rating`.`map_id`=`fastmap_projects`.`id` 
      AND    `rate`='1'
    ) AS `posivote`, 
    ( 
      SELECT count(*) 
      FROM   `fastmap_rating` 
      WHERE  `fastmap_rating`.`map_id`=`fastmap_projects`.`id` 
      AND    `rate`='-1'
    ) AS `negavote` 
  FROM (`fastmap_projects`) 
  LEFT JOIN `my_users` ON `fastmap_projects`.`user_id`=`my_users`.`id` 
)
WHERE `area_id` = '4' 
AND (`negavote` BETWEEN -3 AND 3) 
OR (`posivote` BETWEEN -3 AND 3)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This is your query, formatted so (at least) I can read it:

SELECT my_users.name, my_users.avatar, my_users.surname, fastmap_projects.*,
       (SELECT COUNT(*)
        FROM `fastmap_rating`
        WHERE `fastmap_rating`.`map_id`=`fastmap_projects`.`id` AND `rate`='1') as `posivote`,
       (SELECT COUNT(*)
        FROM `fastmap_rating`
        WHERE `fastmap_rating`.`map_id`=`fastmap_projects`.`id` AND `rate`='-1') as `negavote`
FROM `fastmap_projects` LEFT JOIN
     `my_users`
     ON `fastmap_projects`.`user_id`=`my_users`.`id`
WHERE `area_id` = '4' AND (`negavote` BETWEEN -3 AND 3) OR (`posivote` BETWEEN -3 AND 3)

You are trying to use a column alias in a where clause, and SQL does not allow this (this is part of the standard, not a MySQL limitation). The normal solution is to use a subquery or CTE. However, MySQL has an extension. You can use the having clause instead of a where clause:

SELECT my_users.name, my_users.avatar, my_users.surname, fastmap_projects.*,
       (SELECT COUNT(*)
        FROM `fastmap_rating`
        WHERE `fastmap_rating`.`map_id`=`fastmap_projects`.`id` AND `rate`='1') as `posivote`,
       (SELECT COUNT(*)
        FROM `fastmap_rating`
        WHERE `fastmap_rating`.`map_id`=`fastmap_projects`.`id` AND `rate`='-1') as `negavote`
FROM `fastmap_projects` LEFT JOIN
     `my_users`
     ON `fastmap_projects`.`user_id`=`my_users`.`id`
HAVING `area_id` = '4' AND (`negavote` BETWEEN -3 AND 3) OR (`posivote` BETWEEN -3 AND 3);

As a note: Just check your parentheses to be sure this is what you want to do. This will choose areas that don't have id 4. You may mean this:

HAVING `area_id` = '4' AND (`negavote` BETWEEN -3 AND 3 OR `posivote` BETWEEN -3 AND 3);

Upvotes: 2

Related Questions