CODEWITHSUNDEEP
mysqlsql
cutecutebj
cutecutebj

Reputation: 179

mysql taking one row from a table and minus multiple rows from another table

i have some trouble trying to figure out how to do the following task.

Table 1 

╔═══╦════════════╦═════════════╗
║   ║ Apple      ║ Banana      ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 20         ║ 15          ║
╚═══╩════════════╩═════════════╝

table 2

╔═══╦════════════╦═════════════╦═════════════╗
║   ║ Cat        ║ Banana      ║Apple        ║
╠═══╬════════════╬═════════════╬═════════════╣
║ 1 ║ bbb        ║ 10          ║ 1           ║
║ 2 ║ aaa        ║ 2           ║ 2           ║
║ 3 ║ abc        ║ 1           ║ 3           ║
╚═══╩════════════╩═════════════╩═════════════╝

so there are banana and apple in both table, i would like to take a row from table 1 (which is going to be Apple = 20, and banana = 15) and use these numbers to minus all the rows in table 2's column banana and apple while both table have different ways to order the columns.

i would like to get a result of

apple = 14 ( which is done by doing 20 - 1 - 2 - 3)
banana = 2 (which is done by doing 15 - 10 - 2 - 1)

is there any ways to do this in a query instead of saving the values from somewhere and doing the math one by one?

thanks

Upvotes: 0

Views: 53

Answers (2)

Grzegorz
Grzegorz

Reputation: 121

Probably you can do it by one SQL - but if there will be more data in tables, this will be extremely slow. Better is make 2 separate queries and do math in PHP.

Upvotes: 1

potashin
potashin

Reputation: 44581

You can try something like this:

select t2.Apple - t1.a
     , t2.Banana - t1.b
from (select sum(banana) as b, sum(apple) as a from tbl2) t1
join tbl1 t2

Upvotes: 2

Related Questions