SQL Except or Minus

Question

I have two table with same "id" for companies and i need for "select all" make as minus special companies which do invoice in concrete date.

I tried minus fuctions but its does not work in our ERP systems but i can use "except" but it does not work the way I need it.

SELECT ad.idfirmy FROM aadresar as ad .............\\ (select all)
EXCEPT
SELECT DISTINCT f.idfirmy FROM ddoklfak as f ...... \\ (this i need minus from all sellect)
WHERE modul = 'FAV' and f.datvyst >= '<<datum_od>>' and f.datvyst <= 
'<<datum_do>>') db on db.idfirmy = ad.idfirmy

For examble i have "idfirmy value 193451" which is in second select and after except is still this value in result but its so bad.

I need second select subtract from first

score 0 · Accepted Answer · answered Jan 11 '19 at 07:01

I suggest either you delete the db part

SELECT ad.idfirmy 
FROM aadresar AS ad 
EXCEPT
SELECT DISTINCT f.idfirmy 
FROM ddoklfak AS f 
WHERE modul = 'FAV' AND f.datvyst >= '<<datum_od>>' 
    AND f.datvyst <= '<<datum_do>>';

Or use NOT EXISTS instead

SELECT ad.idfirmy 
FROM aadresar AS ad 
WHERE NOT EXISTS
    (
        SELECT 1 
        FROM ddoklfak AS f 
        WHERE f.idfirmy = ad.idfirmy 
            AND modul = 'FAV' 
            AND f.datvyst >= '<<datum_od>>' 
            AND f.datvyst <= '<<datum_do>>'
    );

THX bad was really db part – Radek Tarant Jan 11 '19 at 07:24 — Radek Tarant, Jan 11 '19 at 07:24

SQL Except or Minus

1 Answers1