yeah i know its tough when u dont know the data structure. Tougher when u dont really know what u are doing lol lmao
I tried the extra distinct in the subquery, but returns the same data. COMMENCEDATE SKILLID NAME TIMEPERIODID WEEKS EMPLOYMENTID ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------- 3/02/2003 4 FORKLIFT LICENCE 1 1 4 3/02/2003 5 HAZ SUB 12 12 4 3/02/2003 8 FIRST AID 16 0 4 19/02/2002 8 FIRST AID 16 0 19 Anyway, i gave up and checked for duplicate skills within the code. >From the few replies i have had in various forums etc, it looks like this must have been the best way cheers anyway ----- Original Message ----- From: "Ian Fear" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Wednesday, February 12, 2003 9:01 PM Subject: Re: [DUG]: sql help please > 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/ > --------------------------------------------------------------------------- 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/
