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!
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!
MySQL Server
Last Comment
seaero
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;
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;
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...
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!
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
ASKER
Version 5.0.26
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
ASKER
Fantastic! That worked perfectly, and I learned something in the process. Thank you!
Regards,
Lynn