Solved

mysql subtract results two queries from each other

Posted on 2010-11-09
7
949 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:michael789
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 

Expert Comment

by:mastermed
ID: 34092728
Can you please, provide sample data and the expected result to be able to help you?
0
 

Author Comment

by:michael789
ID: 34092852
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
 
LVL 6

Expert Comment

by:brb6708
ID: 34092917
... 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
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 

Author Comment

by:michael789
ID: 34093664
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
 
LVL 6

Expert Comment

by:brb6708
ID: 34093914
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
 

Author Comment

by:michael789
ID: 34094100
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
 
LVL 6

Accepted Solution

by:
brb6708 earned 500 total points
ID: 34094177
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question