michael789 asked on # mysql subtract results two queries from each other

I need to get the results from two different queries by substracting results from query A from query B in mySQL. I know how to do it in php however I need this as a mysql query. Is this possible?

Query A:

SELECT * FROM listmain WHERE listmain.fibu1 = 0 AND mitarbeiterid = %s AND listmain.vaz = 1 AND listmain.year2 = YEAR(CURDATE()) UNION SELECT * FROM listmain WHERE listmain.fibu1q = 0 AND mitarbeiterid = %s AND listmain.vaz = 2 AND listmain.year2 = YEAR(CURDATE()) UNION SELECT * FROM listmain WHERE listmain.fibu1y = 0 AND mitarbeiterid = %s AND listmain.vaz = 3 AND listmain.year2 = YEAR(CURDATE())

Query B:

SELECT * FROM listmain WHERE mitarbeiterid = %s AND listmain.vaz <> 0 AND listmain.vaz <> 4 AND listmain.year2 = YEAR(CURDATE()) AND listmain.fibu1 = 1 AND listmain.ust1 = 1

I am actually also interested if there is a possibility to use any Arithmetic Operators between different queries. One way is with UNION for Addition but how can I do this for any other Arithmetic Operators?

thank you

Query A:

SELECT * FROM listmain WHERE listmain.fibu1 = 0 AND mitarbeiterid = %s AND listmain.vaz = 1 AND listmain.year2 = YEAR(CURDATE()) UNION SELECT * FROM listmain WHERE listmain.fibu1q = 0 AND mitarbeiterid = %s AND listmain.vaz = 2 AND listmain.year2 = YEAR(CURDATE()) UNION SELECT * FROM listmain WHERE listmain.fibu1y = 0 AND mitarbeiterid = %s AND listmain.vaz = 3 AND listmain.year2 = YEAR(CURDATE())

Query B:

SELECT * FROM listmain WHERE mitarbeiterid = %s AND listmain.vaz <> 0 AND listmain.vaz <> 4 AND listmain.year2 = YEAR(CURDATE()) AND listmain.fibu1 = 1 AND listmain.ust1 = 1

I am actually also interested if there is a possibility to use any Arithmetic Operators between different queries. One way is with UNION for Addition but how can I do this for any other Arithmetic Operators?

thank you

MySQL Server

mastermed

Can you please, provide sample data and the expected result to be able to help you?

michael789

this is for a financial application where you have the work completed (Query A) per month. All records returned for query A are the records that have not been completed by the employee (listmain.fibu1 = 0)

Now, the employee has however an additional month to complete the work if the field in Query B (listmain.ust1 = 1) is selected for the specific record.

Because the application is going by month I need to substract from all records Query A all records that have listmain.ust1 = 1. The next month I need to do the same however add again the next month the records with listmain.ust1 = 1 that I substracted the previous month.....going on for 12 month

month january = query A month january - query B january

month february = query A month february - query B february + query B january

month march = query A month march - query B march + query B february

I know it is a little bit complicated and that's why I thought it would be good if there would be a way to substract queries from each other...thank you

data.txt

Now, the employee has however an additional month to complete the work if the field in Query B (listmain.ust1 = 1) is selected for the specific record.

Because the application is going by month I need to substract from all records Query A all records that have listmain.ust1 = 1. The next month I need to do the same however add again the next month the records with listmain.ust1 = 1 that I substracted the previous month.....going on for 12 month

month january = query A month january - query B january

month february = query A month february - query B february + query B january

month march = query A month march - query B march + query B february

I know it is a little bit complicated and that's why I thought it would be good if there would be a way to substract queries from each other...thank you

Bernie Bsen

... you need only those results resulting only from one of both queries. Results coming out of both queries should be suppressed. RIGHT?

if yes:

SELECT * FROM listmain

WHERE

(

EXISTS (

SELECT 1 FROM listmain WHERE listmain.fibu1 = 0 AND mitarbeiterid = %s AND listmain.vaz = 1 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1q = 0 AND mitarbeiterid = %s AND listmain.vaz = 2 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1y = 0 AND mitarbeiterid = %s AND listmain.vaz = 3 AND listmain.year2 = YEAR(CURDATE())

)

AND NOT EXISTS (

SELECT 1 FROM listmain WHERE mitarbeiterid = %s AND listmain.vaz <> 0 AND listmain.vaz <> 4 AND listmain.year2 = YEAR(CURDATE())

AND listmain.fibu1 = 1 AND listmain.ust1 = 1

)

OR (

EXISTS (

SELECT 1 FROM listmain WHERE mitarbeiterid = %s AND listmain.vaz <> 0 AND listmain.vaz <> 4 AND listmain.year2 = YEAR(CURDATE())

AND listmain.fibu1 = 1 AND listmain.ust1 = 1

)

AND NOT EXISTS (

SELECT 1 FROM listmain WHERE listmain.fibu1 = 0 AND mitarbeiterid = %s AND listmain.vaz = 1 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1q = 0 AND mitarbeiterid = %s AND listmain.vaz = 2 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1y = 0 AND mitarbeiterid = %s AND listmain.vaz = 3 AND listmain.year2 = YEAR(CURDATE())

)

)

if yes:

SELECT * FROM listmain

WHERE

(

EXISTS (

SELECT 1 FROM listmain WHERE listmain.fibu1 = 0 AND mitarbeiterid = %s AND listmain.vaz = 1 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1q = 0 AND mitarbeiterid = %s AND listmain.vaz = 2 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1y = 0 AND mitarbeiterid = %s AND listmain.vaz = 3 AND listmain.year2 = YEAR(CURDATE())

)

AND NOT EXISTS (

SELECT 1 FROM listmain WHERE mitarbeiterid = %s AND listmain.vaz <> 0 AND listmain.vaz <> 4 AND listmain.year2 = YEAR(CURDATE())

AND listmain.fibu1 = 1 AND listmain.ust1 = 1

)

OR (

EXISTS (

SELECT 1 FROM listmain WHERE mitarbeiterid = %s AND listmain.vaz <> 0 AND listmain.vaz <> 4 AND listmain.year2 = YEAR(CURDATE())

AND listmain.fibu1 = 1 AND listmain.ust1 = 1

)

AND NOT EXISTS (

SELECT 1 FROM listmain WHERE listmain.fibu1 = 0 AND mitarbeiterid = %s AND listmain.vaz = 1 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1q = 0 AND mitarbeiterid = %s AND listmain.vaz = 2 AND listmain.year2 = YEAR(CURDATE())

UNION

SELECT 1 FROM listmain WHERE listmain.fibu1y = 0 AND mitarbeiterid = %s AND listmain.vaz = 3 AND listmain.year2 = YEAR(CURDATE())

)

)

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

michael789

i don't know if this would work

can I not do this or is UNION inside of a UNION not possible?

SELECT *

FROM (

SELECT *

FROM listmain

WHERE listmain.fibu2 =0

AND mitarbeiterid =14

AND listmain.vaz =1

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =0

UNION

SELECT *

FROM listmain

WHERE listmain.fibu1q =0

AND mitarbeiterid =14

AND listmain.vaz =2

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =0

UNION

SELECT *

FROM listmain

WHERE listmain.fibu1y =0

AND mitarbeiterid =14

AND listmain.vaz =3

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =0

)

UNION

SELECT *

FROM listmain

WHERE listmain.fibu1 =0

AND mitarbeiterid =14

AND listmain.vaz =1

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =1

LIMIT 0 , 30

can I not do this or is UNION inside of a UNION not possible?

SELECT *

FROM (

SELECT *

FROM listmain

WHERE listmain.fibu2 =0

AND mitarbeiterid =14

AND listmain.vaz =1

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =0

UNION

SELECT *

FROM listmain

WHERE listmain.fibu1q =0

AND mitarbeiterid =14

AND listmain.vaz =2

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =0

UNION

SELECT *

FROM listmain

WHERE listmain.fibu1y =0

AND mitarbeiterid =14

AND listmain.vaz =3

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =0

)

UNION

SELECT *

FROM listmain

WHERE listmain.fibu1 =0

AND mitarbeiterid =14

AND listmain.vaz =1

AND listmain.year2 = YEAR( CURDATE( ) )

AND listmain.ust1 =1

LIMIT 0 , 30

Bernie Bsen

select * from **tablename** .....

you have a SELECT..... as tablename and this does'nt work.

I had a brief view ower your statement and think it will not bring expected results even if synthax is corrected.

Try my solution in the first post - If I understand your requirement right, it shold work....

you have a SELECT..... as tablename and this does'nt work.

I had a brief view ower your statement and think it will not bring expected results even if synthax is corrected.

Try my solution in the first post - If I understand your requirement right, it shold work....

michael789

thanks..i tried the query you gave above but it goes an error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 28

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 28

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question