enter image description here

There is a database. There are 2 tables "wallets" and "orders" in it. I need to select a wallet from the table "wallets" where month_limit + the amount that the user specified <= 10,000, and the same wallet needs to be checked in the "orders" table, it may be already there and more than once and is waiting for payment, take all the lines With this purse from "orders" and their amount add to month_limit + the amount that the user asked <= 10,000. Advise how best to do here, can you do it all in one request, or do you need to select all purses from "wallets" in the first request the second is to check their availability in the "orders" table and check the value is not more than 10,000?

  • You need the total amount (limit + user_input + sum (orders)) to not exceed 10,000. Or output everything where limit + user <10,000 but in each line show this amount plus the amount from orders? - Mike
  • And do not write in the question " can it be done in one request ", almost everything can be done in one request :) - Mike

2 answers 2

If you display all the wallets where month_limit + $ user_sum and at the same time show this amount plus the amount from orders, then:

SELECT w.*, (SELECT w.month_limit + $user_sum + COALESCE(SUM(o.sum_to_buyer),0) FROM orders o WHERE w.idwallets = o.wallets_idwallets ) as total_sum FROM wallets AS w WHERE month_limit + $user_sum <= 10000

If you select where the total amount, including orders, is less than 10k, then we make it a bit more difficult to add an external query and check for the total amount:

 SELECT * FROM ( SELECT w.*, (SELECT w.month_limit + $user_sum + COALESCE(SUM(o.sum_to_buyer),0) FROM orders o WHERE w.idwallets = o.wallets_idwallets ) as total_sum FROM wallets AS w WHERE month_limit + $user_sum <= 10000 ) A WHERE total_sum<=10000
  • The second request produces an Error Code: 1248. Every derived table must have its own alias, and I need it - rodgers
  • @rodgers Again, the letter is forgotten. Corrected. There the letter A now appeared after the closing bracket :) - Mike
  • @rodgers When asking questions on SQL, try not to lay out the structure of the tables in the form of pictures, but in the text, and directly in the form of SQL create table . If the tables are easy to create, I always check the answers before posting and there are no annoying slip-ups. - Mike
  • Good, thanks - rodgers
  • you can question and the letter "A" which you added, this alias for the sample turned out so? - rodgers

It is possible by one request through JOIN :

 SELECT w.* FROM wallets AS w JOIN orders AS o ON w.idwallets = o.wallets_idwallets HAVING SUM(o.sum_to_buyer) + w.month_limit + $user_sum <= 10000

As I understood, the amount in orders is the sum_to_buyer field.

The variable $ user_sum is the amount entered by the user.

  • SUM cannot be used in where - Mike
  • Produces the error "Error Code: 1111. Invalid use of group function" - rodgers
  • then HAVING is necessary - Klym