Click here to Skip to main content
15,393,955 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Updated 7-Jun-22 19:30pm

1 solution

The problem is that they are distinct entries - they all have different invoice numbers as you would expect.

So SQL can't "tell" that you mean to exclude them - or what criteria you might want to use to exclude them.

If you only one row, then use SELECT TOP 1 will do it, but I suspect that isn't actually what you want.

Sit down with your data, work out exactly what you want to return in terms of rows (define the exact output you want from the query with that input data) and then see what criteria gets you to that. At the moment, we can't help you - we have no more idea what you expect than SQL does!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900