Tracy went to reply last night and then had a closer look at
your sql and thought havent got an answer.
anyway will try now
distinct records are all you can expect
you could try
select distinct pe.commencedate, sn.skillid, s.name, sn.timeperiodid, tp.weeks
from personemployment pe
join skillneeded sn on sn.employmentid = pe.employmentid
left join timeperiod tp on tp.timeperiodid = sn.timeperiodid
join skill s on s.skillid = sn.skillid
where pe.personid = 4
and sn.skillid not in
(select ---Distinct--- skillid from skillattempt where (personid = 4 and skillid =
sn.skillid
and result = 'P' and (validto is null or validto >
'02/11/2003')))
otherwise you need to look at the records and see which
join is causing the duplicates and either restrict the join or
use a group by as suggested, if all the duplicates have the
same details then this might work especially if a min or max
on (may be) time period
if all else fails then a stored procedure with it then else
conditionals may help
Hard to reply without a goodknowlege of the datastructure
etc
Ian Fear
Fear Consulting Ltd
1427 Te Kopia Rd
RD 1
Rotorua
New Zealand
Phone 64 7 333 2548
Fax 64 7 333 2548
mob 025 742 772
[EMAIL PROTECTED]
GMT +1200
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
