How do I remove duplicates with 8 inner joins?

Question

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:

SQL

Select rb.city,
	   ab.PATID,	
	   pb.firstname, 
       pb.lastname,
	   clb.idnum,
       pb.firstvisitdate, 
       pb.lastvisitdate, 
       ab.apptdate,
	   CodeB.adacode,
	   CodeB.description,
	   LogB.Amount,
       InsB.groupname, 
       InsB.insconame,
	   Instb.copayment,
	   Logb.chart_status

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 
		 INNER JOIN DDB_PROC_LOG_BASE AS LogB
			   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 rb.city IN ( 'Anderson')

Posted 27-Feb-20 7:13am

JLeibbrand

Updated 10-Mar-20 10:08am

Add a Solution

Comments

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[^]

2 solutions

Solution 1

Here is an example gotten by a search in Code Project

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

Posted 27-Feb-20 11:42am

MarcusCole6833

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

PeterMehrkens · Accepted Answer · 2020-03-10T10:08:00

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.

e.g.
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
...)