JOIN Query

[annub]

I am joining 5 tables. I get the result but few have dulicate entries not sure why. Please look at my query and tell me whats wrong.

select v.tc03_date,e.date,e.empno,v.total_hrs,sum(e.totaltime)scannerhrs,e.labcode,l.description,t.tc01_name,r.dept
from varreport v,emplhrdtltest e, timeemp t,rate r,labcode l
where v.tc03_employee_no = t.tc01_employee_no
and e.empno = t.tc01_badge_id
and v.tc03_employee_no = r.filenumber
and l.labcode = e.labcode
and v.mydate = substring(e.date,5,4)+substring(e.date,1,2)+substring(e.date,3,2)
and e.date= 11102003
group by e.empno,e.date,v.tc03_employee_no,v.tc03_date,v.total_hrs,e.labcode,t.tc01_name,r.filenumber,t.tc01_badge_id,r.dept,l.description


Result

TDate SDate EmpNo THrs SHrs LabCode LabDesc EmpName
11/10/03 3:15 PM 11102003 3224 8.00 8 26 Materials-Material Handler BAHLOULI, ALI
11/10/03 11:45 PM 11102003 3224 8 26 Materials-Material Handler BAHLOULI, ALI
11/10/03 7:03 AM 11102003 3670 8.00 8 68 Production-Supervisor BARBOSA, CARMEN J
11/10/03 3:15 PM 11102003 3670 8 68 Production-Supervisor BARBOSA, CARMEN J
11/10/03 6:54 AM 11102003 5580 9.00 5 1 Labor BELL, ROBERTA S
11/10/03 6:54 AM 11102003 5580 9.00 3 63 Production-Cleaning lot change BELL, ROBERTA S
11/10/03 3:26 PM 11102003 5580 5 1 Labor BELL, ROBERTA S
11/10/03 3:26 PM 11102003 5580 3 63 Production-Cleaning lot change BELL, ROBERTA S


For Ex Bell Roberta i get 4 records i need labcode 1 & 63 once.

 

[mwolf00]

None of your results are EXACT duplicates - note the different times or amounts. That's why you received more records. Which set do you want, the more recent set or the one with sHrs not null?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)