leila.net leila.net - 3 months ago 12
SQL Question

Inner join without duplicate values from the left table?

I'm working with SQL Server, and I have 2 tables:

Invoice
and
InvoiceService
.

Invoice:

InvoiceID InvoiceDate InvoicePrice InvoicePaidAmount PatientID
----------------------------------------------------------------------------
1 01-01-2016 50 30 1
2 01-02-2016 100 100 2


InvoiceService:

ID InvoiceID ServiceName ServicePrice
-------------------------------------------------
1 1 Dermato 20
2 1 ophthalmo 30
3 2 General 100


My query:

select
ServiceName, ServicePrice, InvoiceID, InvoicePrice,
InvoicePaidAmount, PatientID
from
InvoiceService
inner join
Invoice on Invoice.InvoiceID = InvoiceService.InvoiceID


Result:

ServiceName ServicePrice InvoiceID InvoicePrice InvoicePaidAmount PatientID

Dermato 20 1 50 30 1
ophthalmo 30 1 50 30 1
General 100 2 100 100 2


I need to get non duplicate values from the left table :
when an invoice has more than 1 service I want that the invoice price and InvoicePaidAmount don't be repeated like this example:

ServiceName ServicePrice InvoiceID InvoicePrice InvoicePaidAmount PatientID

Dermato 20 1 50 30 1
ophthalmo 30 1 0 0 1
General 100 2 100 100 2

Answer

If I understand correctly, you want one invoice service to "really" match.

select s.ServiceName, s.ServicePrice, i.InvoiceID,
       (case when seqnum = 1 then i.InvoicePrice else 0 end) as InvoicePrice,
       (case when seqnum = 1 then i.InvoicePaidAmount else 0 end) as InvoicePaidAmount,
       i.PatientID   
from Invoice i join
     (select s.*,
             row_number() over (partition by s.InvoiceID order by s.id) as seqnum
      from InvoiceService s
     ) s
     on i.InvoiceID = s.InvoiceID
Comments