Having trouble excluding duplicates from SQL server query

See more:

SQL

MySQL

Oracle

Microsoft

data

IBM

I have two tables that must be joined. I am trying to return ONLY a list of invoice numbers since they will be used in an outside script. The trouble is duplicate records are being produced and I do not want errors in my accounting records that may result in overcharging. My query produces 17 results when I should only be getting 8 results. As you can see, there are 11 "duplicate" entries which have the same payment (Online), receipt_num (43215Q), account (7226), amount (42.45) and core (22V) fields. They have different invoice_num so they are getting included in my query. I'm not sure have to exclude these duplicates, I only need one of these entries.

THANK YOU !!! I truly appreciate it ...!!!!

I have include a temp table and values.

DECLARE @trans table
(
invoice_num bigint,
period datetime,
payment varchar(30),
receipt_num varchar(30),
account varchar(30),
amount money
)

INSERT INTO @trans VALUES 
('6444874','2022-05-26 07:33','Online','NULL','2278','27.60')
,('6709608','2022-05-26 08:37','Online','29374B','5088','36.33')
,('7527632','2022-05-26 11:14','Local','92643B','8145','80.67')
,('7581937','2022-05-26 11:25','Online','84536T','4607','61.62')
,('7748270','2022-05-26 12:16','Online','12890P','3190','57.00')
,('8432216','2022-05-26 14:13','Online','02602P','0931','42.25')
,('8481985','2022-05-26 14:23','Online','717925','7777','76.00')
,('9172009','2022-05-26 16:28','Online','43215Q','7226','42.45')
,('9219018','2022-05-26 16:31','Online','43215Q','7226','42.45')
,('9223090','2022-05-26 16:35','Online','43215Q','7226','42.45')
,('9227238','2022-05-26 16:38','Online','43215Q','7226','42.45')
,('9228979','2022-05-26 16:40','Online','43215Q','7226','42.45')
,('9245343','2022-05-26 16:41','Online','03333T','5599','37.34')
,('9276168','2022-05-26 16:44','Online','43215Q','7226','42.45')
,('9279193','2022-05-26 16:46','Online','43215Q','7226','42.45')
,('9325767','2022-05-26 16:52','Online','43215Q','7226','42.45')
,('9379138','2022-05-26 17:01','Online','43215Q','7226','42.45')
,('9388957','2022-05-26 17:10','Online','43215Q','7226','42.45')
,('9445007','2022-05-26 17:16','Online','43215Q','7226','42.45')



DECLARE @location table
(
invoice_num bigint,
core varchar(30)
)

INSERT INTO @location VALUES
('6444874','17V')
,('6709608','88V')
,('7527632','27H')
,('7581937','81V')
,('7748270','91V')
,('8432216','32V')
,('8481985','14V')
,('9172009','22V')
,('9219018','22V')
,('9223090','22V')
,('9227238','22V')
,('9228979','22V')
,('9245343','37G')
,('9276168','22V')
,('9279193','22V')
,('9325767','22V')
,('9379138','22V')
,('9388957','22V')
,('9445007','22V')

What I have tried:

SELECT DISTINCT(t.invoice_num)
	FROM @trans t 
INNER JOIN @location p
	ON t.invoice_num = p.invoice_num
AND t.payment = 'Online'
and p.core LIKE '%V%'
and t.amount > 0