Avatar of michael789
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
MySQL Server

Avatar of undefined
Last Comment
Bernie Bsen

8/22/2022 - Mon
mastermed

Can you please, provide sample data and the expected result to be able to help you?
ASKER
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
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())
   )
)

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
ASKER
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
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....
ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Bernie Bsen

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
Sign up - Free for 7 days
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.
See how we're fighting big data
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