How to perform multiple table operations in mysql?

Question

I just want to perform multiple table operations in single query. The query is:

    select name,sno , id  as ids ,(select sum(amount) from client_credits 
where user_id = ids) As total  from clients where sno = '4' and total > '0'

This query is not working when I am trying use ** total > 0 **. Is there any other possibility ways? Please help me.

Total would be a number not a string, have you tried without the quotes around the zero? — scragar, Jun 24 '14 at 07:54

score 2 · Accepted Answer · answered Jun 24 '14 at 07:54

total is an alias. Aliases are not resolved when the WHERE clause is reached.

Try: where sno = 4 having total > 0

Alternatively, and more efficiently:

SELECT `c`.`name`, `c`.`sno`, `c`.`id`, SUM(`ccr`.`amount`) AS `total`
FROM `clients` AS `c`
JOIN `client_credits` AS `ccr` ON `c`.`id`=`ccr`.`user_id`
WHERE `c`.`sno` = 4
GROUP BY `c`.`id`

Notice how the total > 0 part is gone? That's because if there are no rows to join, then nothing will be joined and the rows are removed from the result ;)

Thank you so much !! ` where sno = 4 having total > 0 ` is working fine — Dinesh G, Jun 24 '14 at 08:01

How to perform multiple table operations in mysql?

1 Answers1