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.
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.FNAMEFROM exportb.dbo.LEDGER, exportb.dbo.PATIENTWHERE (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 FNAME2014-11 1111 z1110 None Smith Amy2014-11 1111 z1110 None Smith Amy2014-12 1123 z1110 None Jones Bill2014-12 1123 z1110 None Jones Billetc....The PATIENT table just has PATID and LNAME and FNAME.The LEDGER table would look like this (abbreviated):PATID CODE 1111 Z11101111 D12341111 K12341123 Z11101123 D43121123 J6789Am 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 thisselect l.TRANDATE, l.PATID, l.CODE, l.DESCR, p.LNAME, p.FNAMEFROM exportb.dbo.LEDGER l, exportb.dbo.PATIENT pWHERE (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 queryJaveed Ahmed |
|
|
|
|
|
|
|