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/

Reply via email to