Duplicates in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicates in query

Author

Topic

slaquer
Starting Member

5 Posts

Posted - 2014-07-31 : 20:12:38

SQL newb. I wrote this query:

select exportb.dbo.LEDGER.TRANDATE, exportb.dbo.LEDGER.PATID, exportb.dbo.LEDGER.CODE, exportb.dbo.LEDGER.DESCR, exportb.dbo.PATIENT.LNAME, exportb.dbo.PATIENT.FNAME

FROM exportb.dbo.LEDGER, exportb.dbo.PATIENT

WHERE (exportb.dbo.LEDGER.CODE = 'Z1110') AND (exportb.dbo.LEDGER.PATID = exportb.dbo.PATIENT.PATID) AND ((exportb.dbo.LEDGER.TRANDATE >='2014-7-01 00:00:00') AND (exportb.dbo.LEDGER.TRANDATE <='2014-8-01 00:00:00'))

And it results in this (notice the duplicates):

TRANDATE PATID CODE DESCR LNAME FNAME
2014-11 1111 z1110 None Smith Amy
2014-11 1111 z1110 None Smith Amy
2014-12 1123 z1110 None Jones Bill
2014-12 1123 z1110 None Jones Bill
etc....

The PATIENT table just has PATID and LNAME and FNAME.
The LEDGER table would look like this (abbreviated):

PATID CODE
1111 Z1110
1111 D1234
1111 K1234
1123 Z1110
1123 D4312
1123 J6789

Am I getting dupes because the PATID exists multiple times for some reason? The CODE I am looking for only exists once per PATID so I am confused why I get dupes for each hit.

Using MSSQL query editor to do the queries if that helps.

I am sure this is an obvious answer, or I have the query wrong but I have no clue at this point.

slaquer
Starting Member

5 Posts

Posted - 2014-07-31 : 21:06:40

Forgot to add - I know I could use DISTINCT in the query, but I am wanting to understand why I am getting dupes instead of just "hiding" them using DISTINCT. THANKS

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2014-08-01 : 02:15:41

First of all .U can clean your query to make it understable like this

select l.TRANDATE,
l.PATID,
l.CODE,
l.DESCR,
p.LNAME,
p.FNAME

FROM exportb.dbo.LEDGER l,
exportb.dbo.PATIENT p

WHERE (l.CODE = 'Z1110')
AND (l.PATID = p.PATID)
AND ((l.TRANDATE >='2014-7-01 00:00:00')
AND (l.TRANDATE <='2014-8-01 00:00:00'))

..Also please post the sample data from the both tables and output from your query which you are recieving and output what you are expecting .

Vijay is here to learn something from you guys.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-01 : 02:54:19

ledger table has same patientId multiple times within the date range,so yo will get duplicates,thats the problem with the data not with the query

Javeed Ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources