Link to home
Start Free TrialLog in
Avatar of seaero
seaero

asked on

In MySQL, how do I subtract the column values between 2 queries all in a single query?

I have 2 separate queries that return identically structured tables. I will need to combine the two tables such that the result table containes the difference between a column in both tables. In other words:

Query1:  (Select id1,qty1 from TableA Left Join TableB)

id1    qty1
----------
1      10
2      20
3      8
4      5
5      3

Query2: (Select id2,qty2 from TableC Left Join tableD)

id2    qty2
----------
1      3
2      16
3      2
4      0
6      1

Result: (Query1 - Query2)
ID    QTY
----------
1      7
2      4
3      6
4      5
5      3

As the result indicates, I will need to retain all ids returned from Query 1 but not Query 2.

Thanks!
Avatar of JesterToo
JesterToo
Flag of United States of America image

Your query syntax seems to be missing some elements... which columns are coming from which tables and what columns are you joining on?  Sample data for all tables in the queries would help.

Regards,
Lynn
Avatar of SkywireMoncton04
SkywireMoncton04

Hi there,

I am confused by "retain all ids returned from..."  It looks like you're subtracting the quantity from the ID from the first query to the same ID of the second query.  

I was able to replicate what you have by having two tables and blend both queries to return the resultset you are looking for.  Add your joins as applicable.  I hope this helps.

select id1, qty1 - (Select qty2 from tableb where id2 = id1) as QTY from tablea;  


Avatar of seaero

ASKER

I am sorry for the confusion. It's not actual query syntax that I posted. It's merely pseudocode. I should have stated that.

What I meant with "retain all ids returned from..." is that query1 can have ids that don't exist in query2 and vice versa. In the result table, I would like to have only the ids returned from query1. Query2 results can be looked at as a reference table.

Sorry again for the confusion. I will be clearer next time...

SkywireMoncton04, I will try your suggestion now...
Having the where id2 = id1 would eliminate any ids that are not present in either query.  Good luck!
Avatar of Muhammad Khan
which mysql database version are you using... with current info .. i can only suggest a subquery solution.. which is supported in versoin 5 of mysql... not before
Avatar of seaero

ASKER

Version 5.0.26
ASKER CERTIFIED SOLUTION
Avatar of Muhammad Khan
Muhammad Khan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the one i used here is not a subquery... it is rather called an inline view.... u need to check if mysql supports this... oracle and sqlserver do
Avatar of seaero

ASKER

Fantastic! That worked perfectly, and I learned something in the process. Thank you!