How to use Having without Selecting field in mysql

Question

Im using this query:

    SELECT  `projects`.*, 
            (SELECT SUM(`amount`) 
            FROM `accountprojectspayment` 
            WHERE `projects_id` = `projects`.`id`) AS `payed`
    FROM `projects`
    INNER JOIN `bids` ON `bids`.`id` = `projects`.`bids_id`
    HAVING `bids`.`amount` >= `payed`

i get this error: Unknown column 'bids.amount' in 'having clause

But if i change the code to this:

    SELECT  `projects`.*, `bids`.`amount`,
            (SELECT SUM(`amount`) 
            FROM `accountprojectspayment` 
            WHERE `projects_id` = `projects`.`id`) AS `payed`
    FROM `projects`
    INNER JOIN `bids` ON `bids`.`id` = `projects`.`bids_id`
    HAVING `bids`.`amount` >= `payed`

the problem get solved but i do not want to use Select bids.amount

onedaywhen · Accepted Answer · 2011-11-08 15:11:11Z

4

Use a derived table e.g. something like this:

SELECT `DT1`.* 
FROM   (SELECT  `projects`.*, 
                (SELECT SUM(`amount`) 
                FROM `accountprojectspayment` 
                WHERE `projects_id` = `projects`.`id`) AS `payed`                   
        FROM `projects`) AS `DT1`            
INNER JOIN `bids` ON `bids`.`id` = `DT1`.`bids_id`
WHERE `bids`.`amount` >= `DT1`.`payed`;

answered Nov 8, 2011 at 15:11

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use Having without Selecting field in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related