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
michael789Asked:
Who is Participating?
 
brb6708Connect With a Mentor Commented:
My query does not contain 'LIMIT 0, 30......

Here it is again - can not try it mysqlf becouse I don't know your table layout.


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())
   )
)

0
 
mastermedCommented:
Can you please, provide sample data and the expected result to be able to help you?
0
 
michael789Author Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
brb6708Commented:
... 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())
   )
)

0
 
michael789Author Commented:
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
0
 
brb6708Commented:
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....
0
 
michael789Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.