CODEWITHSUNDEEP
sqlsql-server
savethesavior
savethesavior

Reputation: 9

Unselect all records of same customer if a condition met

I have two data tables:

Table KHACHHANG:

MAKH    TENKH              DIACHI       DT       EMAIL
----------------------------------------------------------------------------
KH01    NGUYEN THI BE      TAN BINH     8457895  [email protected]
KH02    LE HOANG NAM       BINH CHANH   9878987  namlehoang @abc.com.vn
KH03    TRAN THI CHIEU     TAN BINH     8457895 
KH04    MAI THI QUE ANH    BINH CHANH       
KH05    LE VAN SANG        QUAN 10               [email protected]
KH06    TRAN HOANG KHAI    TAN BINH     8457897

Table HOADON:

MAHD    NGAY        MAKH
------------------------
HD001   12/05/2000  KH01
HD002   25/05/2000  KH02
HD003   25/05/2000  KH01
HD004   25/05/2000  KH04
HD005   26/05/2000  KH04
HD006   02/06/2000  KH03
HD007   22/06/2000  KH04
HD008   25/06/2000  KH03
HD009   15/08/2000  KH04
HD010   30/09/2000  KH01

I want to list all customers that did not place order in June 2000. This is my code:

select distinct KHACHHANG.MAKH, DIACHI, DT
    from KHACHHANG
    left join HOADON
    on HOADON.MAKH = KHACHHANG.MAKH
    where MONTH(HOADON.NGAY)<>6 and YEAR(HOADON.NGAY)=2000
        or KHACHHANG.MAKH not in (select MAKH from HOADON)

But the result was:

MAKH  DIACHI                                             DT
----- -------------------------------------------------- ----------
KH01  TAN BINH                                           8457895
KH02  BINH CHANH                                         9878987
KH04  BINH CHANH                                         NULL
KH05  QUAN 10                                            NULL
KH06  TAN BINH                                           8457897

The record of Customer ID KH04 should not appear in the result because he bought something on 22/06/2000.
So how can I remove the KH04 from the result ?
Thank you.

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You seem to want not exists:

select k.*
from KHACHHANG k
where not exists (select 1
                  from HOADON h
                  where h.MAKH = k.MAKH and
                        h.ngay >= '2000-06-01' and
                        h.ngay < '2000-07-01'
                 );

I would recommend an index on HOADON(MAKH, NGAY).

Note that I changed the date comparisons. This makes them more amenable to using an index.

Upvotes: 2

forpas
forpas

Reputation: 164099

You can do it with not exists:

select k.*
from KHACHHANG k
where not exists (
  select 1 from HOADON
  where makh = k.makh and MONTH(ngay) = 6 and YEAR(ngay)=2000  
)

Upvotes: 0

Related Questions