CODEWITHSUNDEEP
mysqlsqlstored-procedures
merlin
merlin

Reputation: 81

How to subtract a query result from another query result in MYSQL

1st query 

   (SELECT a.cat_id, 
          a.cat_name, 
          a.cat_description, 
          b.subcat_name, 
          b.subcat_description 
     FROM trade_categories a 
LEFT JOIN trade_subcategories b ON a.cat_id = b.cat_id 
    WHERE a.cat_name LIKE '%catty%' 
       OR a.cat_description LIKE '%catty%') 
UNION 
  (SELECT c.cat_id, 
          d.cat_name, 
          d.cat_description, 
          c.subcat_name, 
          c.subcat_description 
     FROM trade_subcategories c 
LEFT JOIN trade_categories d ON c.cat_id = d.cat_id 
    WHERE c.subcat_name LIKE '%catty%' 
       OR c.subcat_description LIKE '%catty%')

2nd query :

SELECT x.cat_id, 
       x.cat_name, 
       x.cat_description, 
       y.subcat_name, 
       y.subcat_description 
 FROM  trade_categories x 
  JOIN trade_subcategories y ON x.cat_id = y.cat_id 
 WHERE (   x.cat_name LIKE '%catty%' 
        OR x.cat_description LIKE '%catty%' ) 
   AND (   y.subcat_name NOT LIKE '%catty%' 
        OR y.subcat_description NOT LIKE '%catty%' )

I want to subtract the 2nd query result from 1st query result.

Upvotes: 6

Views: 21528

Answers (3)

Bart
Bart

Reputation: 4920

http://www.bitbybit.dk/carsten/blog/?p=71

or example:

SELECT Name FROM employee1  WHERE name NOT IN (SELECT name FROM employee2);

Upvotes: 13

Andriy M
Andriy M

Reputation: 77687

   SELECT a.cat_id, 
          a.cat_name, 
          a.cat_description, 
          b.subcat_name, 
          b.subcat_description 
     FROM trade_categories a 
LEFT JOIN trade_subcategories b ON a.cat_id = b.cat_id
    WHERE a.cat_name NOT LIKE '%catty%' 
      AND a.cat_description NOT LIKE '%catty%'
      AND (b.cat_id IS NULL
        OR b.subcat_name NOT LIKE '%catty%' AND b.subcat_description NOT LIKE '%catty%')

Or, if the results of the two queries have been stored in (temporary) tables, you could use @Abhay's solution on them.

Upvotes: 0

Abhay
Abhay

Reputation: 6645

I think you can either do a NOT IN or a LEFT JOIN. I'd prefer a LEFT JOIN.

So for example,

SELECT `QUERY_ONE`.*
FROM `QUERY_ONE`
LEFT JOIN `QUERY_TWO` USING (`cat_id`)
WHERE QUERY_TWO.cat_id IS NULL;

where QUERY_ONE and QUERY_TWO are aliases for your two queries

Upvotes: 1

Related Questions