Tracey I think the moral of the story is that SQL will do anything but not necesarily in a single query
One of the things MS Access (Jet) extensions of SQL did was return the first record on a break condition (Sort of what you wanted), which can't be done in std SQL without a cursor Neven ----- Original Message ----- From: "Tracey Maule" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 9:36 AM Subject: Re: [DUG]: sql help please > 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/ > > --------------------------------------------------------------------------- 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/
