Solved

mysql subtract results two queries from each other

Posted on 2010-11-09
7
940 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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