如何与不在一起工作 [英] How to work with not in

问题描述

嗨朋友

我有一张名为attachment_marche的表

Hi friends
I have a table named "attachment_marche"

create table attachement_marche
(
Id_attachement_marche int primary key identity NOT NULL,
Id_decompte int foreign key references decompte (Id_decompte),
Id_bon_reception_marche int foreign key references bon_reception_marche(Id_bon_reception_marche),
Id_marche int foreign key references marche(Id_marche),
Num_attachement varchar(100) COLLATE French_CI_AS,
Date_etablissement date,
Date_debut date,
Date_fin date,
Flag_dernier varchar(20)COLLATE French_CI_AS,
Montant float,
Ind_supp int DEFAULT 1,
User_create varchar(100) COLLATE French_CI_AS NOT NULL,
Date_create datetime DEFAULT getdate() NOT NULL,
User_modif varchar(100) COLLATE French_CI_AS ,
Date_modif varchar(100) COLLATE French_CI_AS
)

和一个名为bon_reception_marche的表

And a table named bon_reception_marche

create table bon_reception_marche
(
Id_bon_reception_marche int primary key identity NOT NULL,
Id_marche int foreign key references marche(Id_marche),
Designation_bon_reception varchar(100) COLLATE French_CI_AS,
Num_bon_reception varchar(100) COLLATE French_CI_AS,
Date_commande date,
Date_reception date,
Unite varchar(50) COLLATE French_CI_AS,
Qte float,
Prix_unitaire float,
Montant float,
TVA float,
MO varchar(100) COLLATE French_CI_AS,
OT varchar(100) COLLATE French_CI_AS,
Ind_supp int DEFAULT 1,
User_create varchar(100) COLLATE French_CI_AS NOT NULL,
Date_create datetime DEFAULT getdate() NOT NULL,
User_modif varchar(100) COLLATE French_CI_AS ,
Date_modif varchar(100) COLLATE French_CI_AS,
)
I want to display the vouchers of receipts which are between two dates and which are not affected to any attachment of the marche

并提前感谢朋友们



我的尝试：



我测试了这个请求但没有工作

And thanks in advance friends

What I have tried:

I tested this request but did not work

select * from bon_reception_marche where
Id_bon_reception_marche NOT IN
(select Id_bon_reception_marche from attachement_marche where Id_marche in
(select Id_marche from marche where Num_marche = 'DG/01/2014'))
and Date_reception between '2016-07-07' and '2017-06-19' and
Id_marche in (select TOP 1 Id_marche from marche where Num_marche = 'DG/01/2014')

推荐答案

你没有不提供任何数据样本。但是根据您的意见，您想要显示附件表中不存在的收据列表，请检查以下查询。

You didn't give any data sample. But as per ur comments that You want to display list of receipts which does not exists in the attachment table, then check the below query.

SELECT * 
FROM BON_RECEPTION_MARCHE A
WHERE DATE_RECEPTION BETWEEN '2016-07-07' AND '2017-06-19' 
AND ID_BON_RECEPTION_MARCHE NOT IN (SELECT ID_BON_RECEPTION_MARCHE FROM ATTACHEMENT_MARCHE WHERE ID_MARCHE IN 
(SELECT ID_MARCHE FROM MARCHE WHERE NUM_MARCHE = 'DG/01/2014'))

这篇关于如何与不在一起工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！