Possible duplicates to find in SQL

Question

I've been trying the following suggestions from this post, but this does not apply for my case or at least I am not capable of adapting the query to my needs.

I have three tables: one stands for documents header, one stands for documents lines and one stands for item's information (Code, Description etc).

I would like to extract all the documents number that have the same value (this is the information from the documents header table), the same items (the code of the item) and the same quantity (from the lines of the documents tables). How can I extract this information? Thanks

The tables are -

DocHeader              DocLines             Items

ID                     fDocID               ID 
Code                   fItemID              Code
Date                   Quantity             Description
----                   --------             -----------
TotalValue             etc                  etc

Later edit

Output should like something like:

DocCode    ItemCode   Quantity   TotalValue
01         001            5        1000
01         002            5        1000
01         003            4        1000 
02         001            5        1000 
02         002            5        1000
02         003            4        1000

DDL

create table DocHeader 
(
    Id bigint not null identity(1,1) primary key clustered
    , Code nvarchar(32) not null
    , [Date] datetime not null
)
go
create table Items 
(
    Id bigint not null identity(1,1) primary key clustered
    , Code nvarchar(32) not null
    , [Description] nvarchar(256)
    , UnitPrice money not null
)
go
create table DocLines
(
    Id bigint not null identity(1,1) primary key clustered
    ,fDocId bigint not null constraint fk_DocLines_fDocId foreign key references DocHeader(Id)
    ,fItemId bigint not null constraint fk_DocLines_fDocId foreign key references Items(Id)
    ,Quantity int not null
)
go
create view vDocHeader as
select dh.*
, x.TotalValue
from DocHeader
left outer join 
(
    select dl.fDocId
    , sum(dl.Quantity * i.UnitPrice) TotalValue
    from DocLines dl
    inner join Items i
    on i.Id = dl.fItemId
    group by dl.fDocId
) x
on x.fDocId = dh.Id

If you really want help. Then provide DDL for the table, INSERT statements to populate test data and then expected result — Dmitrij Kultasev, Oct 06 '17 at 09:40
@DmitrijKultasev - the documents and the lines are inserted from a software. Anyway, I don't think this is relevant, but see my edited post. — cdrrr, Oct 06 '17 at 09:48

score 0 · Answer 1 · answered Oct 06 '17 at 11:16

Why not simple grouping?

SELECT dh.Code AS DocCode, i.Code AS ItemCode, Quantity, SUM(dl.Quantity * i.UnitPrice) AS TotalValue
FROM DocHeader dh
LEFT JOIN DocLines dl ON dl.fDocId=dh.id
JOIN Items i ON i.Id = dl.fItemId
GROUP BY dh.Code,i.Code,Quantity

Possible duplicates to find in SQL

1 Answers1