MySQL The correlation query clause does not work.

Question

I have a problem in my article. I want to compare between two amounts having the same bet, but with different fields. I get this output:

SELECT v.bid FROM v1 v WHERE sens = 'c' GROUP BY bid HAVING Sum(mont) < (SELECT Sum(l.mont) FROM v1 l WHERE sens = 'd' AND l.bid = v.bid group by l.bid); 

ERROR 1054 (42S22): Field 'v.bid is unknown in the list of fields`

Edit: V1 is a view, I used the aliases l and v, trying to translate the subquery into the main query

Sorry guys, thank you all for your answers, I had a problem with the columns of the source table, and now it is solved :)

Answer 1

There is no need for two options from this table, you can use CASE EXPRESSION for this purpose:

 SELECT v.bid FROM v1 v GROUP BY v.bid HAVING SUM(CASE WHEN v.sens = 'c' THEN v.mont ELSE 0 END) < SUM(CASE WHEN v.sens = 'd' THEN v.mont ELSE 0 END) AND COUNT(CASE WHEN v.sens = 'c' THEN 1 END) > 0 

Answer 2

The alias in the subquery is not displayed / available, so you get and the column is not found error, but you can reorganize your queru in this way

  select v.bid, t.tot from v1 v Join ( select bid, sum(mont) tot from v1 where sens = 'd' group by bid ) t on t.bid = v.bid where v.sens = 'c' and v.bid < tot 

Answer 3

I had a problem with the visibility of the columns because the view was incorrectly declared, I apologize guys: "(I feel really bad :(

script:

 mysql> select * from op; +------+------+------+------+ | bid | cid | sens | mont | +------+------+------+------+ | 1 | 1 | c | 2000 | | 1 | 1 | c | 2000 | | 1 | 1 | c | 2000 | | 1 | 1 | c | 2000 | | 1 | 2 | c | 2000 | | 1 | 3 | c | 2000 | | 2 | 3 | c | 2000 | | 1 | 1 | d | 2000 | | 2 | 3 | d | 4000 | +------+------+------+------+ 9 rows in set (0.00 sec) create view v1 as ( select bid , cid , sens , sum(mont) as sumcli from op group by bid,cid,sens); mysql> select * from v1 ; +------+------+------+--------+ | bid | cid | sens | sumcli | +------+------+------+--------+ | 1 | 1 | c | 8000 | | 1 | 1 | d | 2000 | | 1 | 2 | c | 2000 | | 1 | 3 | c | 2000 | | 2 | 3 | c | 2000 | | 2 | 3 | d | 4000 | +------+------+------+--------+ 6 rows in set (0.00 sec) mysql> SELECT v.bid -> FROM v1 v -> WHERE sens = 'c' -> group by v.bid -> HAVING Sum(v.sumcli) < (SELECT Sum(l.sumcli) -> FROM v1 l -> WHERE sens = 'd' -> AND l.bid = v.bid group by l.bid); +------+ | bid | +------+ | 2 | +------+ 1 row in set (0.00 sec)