Click here to Skip to main content
14,694,397 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am not sure if anyone will be interested in helping a self starter without much knowledge. I have ordered an SQL book but do not have it yet.
I am getting data and have tried select distinct multiple ways and a union command, no luck removing duplicates.
The patid, appt date and the adacode need to drive the removal of duplicates. When i do the Select distinct I only get two fields. I tried more fields and got errors. I tried the Union and same thing errors. Because I am starting out and not knowledgeable enough, I need help! My code is below.

What I have tried:


FROM   ddb_appt_base AS ab
		INNER JOIN ddb_pat_base AS pb 
               ON ab.patid = pb.patid 
		 INNER JOIN ddb_insured_base AS ib 
               ON pb.patid = ib.insuredid 
		 INNER JOIN ddb_insurance_base AS InsB 
               ON ib.insid = InsB.insid 
			   ON ab.PATID = LogB.PATID
		 Inner Join DDB_PROC_CODE AS CodeB
			   On codeb.proc_codeid = LogB.proc_codeid
		 Inner Join DDB_RSC_BASE AS rb
		       On rb.urscid = Logb.clinicappliedto
		 Inner Join DDB_INSTABLE_BASE As Instb	
			   On ib.insid = instb.insid
		 Inner Join ddb_claim_base AS clb
			   ON clb.patid = ab.patid

WHERE  ab.apptdate BETWEEN '2016-05-01 00:00:00.000' AND 
                           '2017-04-30 00:00:00.000' 
       AND InsB.insid IN ( '1002420', '1002707', '1003104', '1003096',
                           '1002814', '1002820', '1002439', '1002579', '1003132' 
						  AND IN ( 'Anderson')
Updated 10-Mar-20 11:08am
Richard Deeming 27-Feb-20 14:29pm
If you want help fixing an error, you need to provide the full details of the error.

If you want help fixing your query, you need to show us a sample of the input data, your desired output from that sample, and what your query is currently producing.

Preferable, create a SQL Fiddle[^] with some dummy data to replicate the problem.

NB: DISTINCT applies to ALL columns in your results. If you want distinct records based on a subset of columns, then you'll need to use GROUP BY, and choose an aggregate function for the other columns you're returning.

GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]

Ok, so I can tell by the table names, you are querying against a Dentrix database.

Almost every table has composite keys and you are missing fields in your join clause. The pattern is PatId, PatDb; InsId, InsDb; InsuredId, InsurdDb; etc. For one appointment record, you might get additional, incorrect, patients who share the same id value, but different db value. Then you might get additional, incorrect, insurances. Etc.

It appears you are getting duplicates, but if you drill down, they are distinct.

INNER JOIN ddb_pat_base AS pb ON ab.patid = pb.patid
should be
INNER JOIN ddb_pat_base AS pb ON ab.patid = pb.patid AND ab.patdb = pb.patdb

It also means you can't use your IN clause.

it needs to be something like:
AND ((InsB.insid = '1002420' AND insB.insdb = ##) OR
(InsB.insid = '1002707' AND InsB.insdb = ##) OR
JLeibbrand 13-Mar-20 9:21am
Thank you so much for this information, it help tremendously, I am just teaching myself and trying to get a specific report. I there anywhere other than the Database dictionary that gives you clues as to where fields are. I am trying to find the actual verbage from the ADACode on the ledger for payments, guarantor, insurance, etc. Can only find the description that just says "payment-do not delete".
PeterMehrkens 2-Jul-20 12:52pm
I don't and I don't have consistent access to a Dentrix DB to check.

I would have guessed Proc_Log or Proc_code. But it might come from different sources depending on the transaction.

Typically I'd join a bunch of tables I think are likely, export the results, and search for some examples I'm seeing. You could also make educated guesses at what they might name a column: select, from sys.tables as t inner join sys.columns as c on t.object_id = c.object_id where like '%desc%'
Here is an example gotten by a search in Code Project

Identifying and Deleting Duplicate Records from SQL Server Table[^]

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