I have two data tables:
Table KHACHHANG:
MAKH TENKH DIACHI DT EMAIL ---------------------------------------------------------------------------- KH01 NGUYEN THI BE TAN BINH 8457895 bnt@yahoo.com 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 sanglv@hcm.vnn.vn 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.
Advertisement
Answer
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 )