sqlsql-serverdevexpress

SQL one to many join table issue


I have 2 tables which is one to many relationship. I want to join both table but the result shown after join is not what I need. Below is the table and sample data.

Create table #temp (
    DONo varchar(10),
    Amount decimal(18,2),
    OrderNo varchar(10)
)

Create table #order (
    Item varchar(10),
    Amount decimal(18,2),
    OrderNo varchar(10),
    PackingDate date
)

Insert into #temp(DONo, Amount, OrderNo)
VALUES('A123', 1000, 'O123')
Insert into #temp(DONo, Amount, OrderNo)
VALUES('A124', 1100, 'O124')

Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES('O123', 'Pen', 100, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Color', 800, '2024-02-01')
Insert into #order (OrderNo, Item, Amount, PackingDate)
VALUES ('O123', 'Box', 100, '2024-02-02')

I have try below join query

select 
   do.DONo, do. Amount, do.OrderNo, SUM(so.Amount)[[OrderAmount], so.PackingDate 
from #temp do
left join
  #order so
on
  do.OrderNo = so.OrderNo
group by
  do.DONo, do. Amount, do.OrderNo, so.PackingDate

Here is the output for above query

DONo    DOAmount  OrderNo   Amount     PackingDate
A123    1000.00   O123     900.00     2024-02-01
A123    1000.00   O123     100.00     2024-02-02
A124    1100.00   O124      NULL      NULL

The expected output I wish as below

DONo    DOAmount  OrderNo   Amount     PackingDate
A123    1000.00   O123     900.00     2024-02-01
A123              O123     100.00     2024-02-02
A124    1100.00   O124      NULL      NULL

Since the DO no is same for order O123, the amount for DO I need shown once. The reason why I need show the DO amount once is because when in the visualization, the tools will sum up the DO amount and it will become double up in the bar chart.

I think my direction is wrong and my table design was wrong. Please guide me how I can fixed it and show the result I need.


Solution

  • just replace

    do.Amount,
    

    by this

     case when row_number() over (partition by do.DONo order by do.DONo) = 1 then do.Amount else null end as Amount,
    

    full query

    select do.DONo, 
           case when row_number() over (partition by do.DONo order by do.DONo) = 1 then do.Amount else null end as Amount,
           do.OrderNo, 
           sum(so.Amount)OrderAmount, 
           so.PackingDate 
    from temp do
      left join orders so on do.OrderNo = so.OrderNo
    group by
      do.DONo, do. Amount, do.OrderNo, so.PackingDate
    

    result

    DONo Amount OrderNo OrderAmount PackingDate
    A123 1000.00 O123 900.00 2024-02-01
    A123 null O123 100.00 2024-02-02
    A124 1100.00 O124 null null

    See this demo