Solved

mysql subtract results two queries from each other

Posted on 2010-11-09
7
945 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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access - need to reduce row size 25 65
sort in mysql based off of query param 4 30
Combining Queries 7 43
How do I Enable submit button only if listbox has items 4 35
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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