I have two tables with this structure:
First table: Document
Doc_Num | Doc_Type | Doc_Date | Doc_Valide |
---|---|---|---|
BLA241913 | BLA | 01/07/2024 | 1 |
Second table: DocumentD
Doc_Num | DocDOrigine |
---|---|
BLA241913 | CMA241682 |
I try many queries with self join, inner join, left join but did not get desired result.
My query:
SELECT *
FROM Documentd
INNER JOIN [Document] ON DocumentD.Doc_Num = [Document].Doc_Num
WHERE ([Document].Doc_Type IN (N'bla', N'faca'))
AND (DocumentD.DocD_QReliquat >= 0)
AND ([Document].Doc_Date BETWEEN '2024-07-01' AND '2024-07-31')
This query return as result:
Doc_Num | Doc_Type | Doc_Date | DocDOrigine | Doc_Valide |
---|---|---|---|---|
BLA241913 | BLA | 01/07/2024 | CMA241682 | 1 |
BLA241913 | BLA | 01/07/2024 | CMA241682 | 1 |
BLA241913 | BLA | 01/07/2024 | CMA241682 | 1 |
BLA241914 | BLA | 01/07/2024 | CMA241683 | 1 |
BLA241914 | BLA | 01/07/2024 | CMA241683 | 1 |
BLA241915 | BLA | 01/07/2024 | CMA241684 | 1 |
BLA241916 | BLA | 01/07/2024 | CMA241685 | 1 |
BLA241917 | BLA | 01/07/2024 | CMA241686 | 0 |
ACHAT240730008 | FACA | 01/07/2024 | BLA241913 | 2 |
ACHAT240730008 | FACA | 01/07/2024 | BLA241913 | 2 |
ACHAT240730008 | FACA | 01/07/2024 | BLA241913 | 0 |
ACHAT240730010 | FACA | 01/07/2024 | BLA241914 | 0 |
ACHAT240730010 | FACA | 01/07/2024 | BLA241914 | 1 |
ACHAT240731025 | FACA | 01/07/2024 | null | 1 |
I want to show all [Document].Doc_num
with [Document].Doc_Type
= ‘BLA’ and [Document].Doc_Valide
= 1 , with condition this Documents not exist in DocDOrigine
with [Document].Doc_Type
= ‘FACA’ like this:
Doc_Num | Doc_Type | Doc_Date | DocDOrigine | Doc_Valide |
---|---|---|---|---|
BLA241915 | BLA | 01/07/2024 | CMA241684 | 1 |
BLA241916 | BLA | 01/07/2024 | CMA241685 | 1 |
I wish I had been clear in posing my question, and I am here to clarify thank you.
Link : https://sqlfiddle.com/sql-server/online-compiler?id=e31a94d2-3778-4861-870f-7b0c6730a7d7
7
I think I understand the question. I’m not sure if this is the right answer, but it returns the records you specified.
select *
from Document
where
Document.Doc_Num not in (select DocDOrigine from DocumentD) and
Doc_Type = 'BLA' and
Doc_Valide = 1;
Basically, the first WHERE clause is what you were missing. This is one of the easiest ways to write that, but it could easily have performance issues or need its own filtering to match better with the Document
table.
Here’s another way it can be done, using an outer join instead of a sub-query:
select Document.*
from Document
left outer join DocumentD on
Document.Doc_Num = DocumentD.DocDOrigine
where
DocumentD.DocDOrigine is null and
Document.Doc_Type = 'BLA' and
Document.Doc_Valide = 1;
Edit:
I missed the DocDOrigine column from the example result set. I had to add some missing data from your question to the SQL fiddle to get it to return the expected result set. The SQL Fiddle link is:
https://sqlfiddle.com/sql-server/online-compiler?id=8af184de-67b4-49cf-a4fe-50148dc9b4eb
The queries to return that are:
select
Document.Doc_Num,
Document.Doc_Type,
Document.Doc_Date,
DocumentD.DocDOrigine,
Document.Doc_Valide
from Document
inner join DocumentD on
Document.Doc_Num = DocumentD.Doc_Num
where
Document.Doc_Num not in (select DocDOrigine from DocumentD) and
Doc_Type = 'BLA' amd
Doc_Valide = 1;
And, the outer join variant:
select
Document.Doc_Num,
Document.Doc_Type,
Document.Doc_Date,
PrimDocument.DocDOrigine,
Document.Doc_Valide
from Document
left outer join DocumentD as OrigDocument on
Document.Doc_Num = OrigDocument.DocDOrigine
inner join DocumentD as PrimDocument on
Document.Doc_Num = PrimDocument.Doc_Num
WHERE
OrigDocument.DocDOrigine is null and
Document.Doc_Type = 'BLA' and
Document.Doc_Valide = 1;