calling sql gurus - Firebird

[Tracey]

Hi to all u SQL Gurus out there!

could someone help me with my query please?

I have the following tables

Skill Attempt: //holds all attempts at "skill" even failed and disqualified (for licences)
SkillAttemptID //eg licence attempts etc
PersonID
AttemptDate
ValidTo
ProviderDI
SkillID
Result
Ref
Comments

Skill: //holds names of skills and time valid
SkillID
Name
TimeValid
CompanyID

SkillNeeded: //holds what skills are required for what job
SkillNeededID
EmploymentID
TimePeriodID
SkillID

Employment: //job names
EmploymentID
Name

TimePeriod: //time periods in weeks for select box
TimePeriodID
Weeks

PersonEmployment: //who does what jobs
PersonEmploymentID
CommenceDate
PersonID

PErson:
personid
name
etc

In the SkillNeeded table, some skills are required within a certain period of beginning a job. these hold a foreign key linking to timeperiod
if the skill is a prerequisite, the value will be 0
if there is no time limit, the skillneeded.timeperiodid will be null - hence the left join

i have the following sql to show what skills a person requires that they do not yet have:

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 skillid from skillattempt where (personid = 4 and skillid = sn.skillid
and result = 'P' and (validto is null or validto > '02/11/2003')))

Some skills are required in more than one job. If this person has both those jobs and doesnt have that skill, then the query returns the skill for as many times as it is needed.

What i really want, is for the skill itsself to be only returned once, with the earliest timeperiod. Is this possible in SQL or do i need to frig with code? (distinct isnt working with all my joins)

 

[McMerfy]

Hey there. You know if you gave us a few lines returned by your query and told us which one you want to be the only one returned, then we would have more chances to help you. Anyway maybe this would help:

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)
  AND(TP.TIMEPERIODID = (SELECT MAX(TP2.TIMEPERIODID) 
      FROM TIMEPERIOD TP2))
 join skill s on (s.skillid = sn.skillid)
where (pe.personid = 4)and
      sn.skillid not in
(select skillid from skillattempt where (personid = 4 and skillid = sn.skillid
and result = 'P' and (validto is null or validto > '02/11/2003')))

HTH

--- markus

 

[McMerfy]

And by the way: in Interbase JOIN and LEFT JOIN mean the same.

--- markus

 

[Tracey]

Thanks for your reply McMerfy

The query i ended up using:

select distinct pe.commencedate, sn.skillid, s.name, sn.timeperiodid, tp.weeks
from personemployment pe
join skillneeded sn on sn.employmentid = pe.employmentid
join timeperiod tp on tp.timeperiodid = sn.timeperiodid
join skill s on s.skillid = sn.skillid
where pe.personid = 1
and sn.skillid not in
(select skillid from skillattempt where (personid = 1 and skillid = sn.skillid
and result = 'P' and (validto is null or validto > '2/11/2003')))
order by name, weeks

returns exactly the same as yours.
I ended up doing what i needed in code, checking each skill is not the same as the last etc etc.

both these queries returned the following recordset:

EMPLOYMENTID COMMENCEDATE       SKILLID    TIMEPERIODID DATEBY             WEEKS      PERSONID   
-------------------------------------------------------------------------------------------------
4            3/02/2003          4          1                                          1          
4            3/02/2003          5          12                                         1          
4            3/02/2003          8          16                              0          1          
19           19/02/2002         8          16                              0          1

not very good example data but essentially i only wanted once record returned for each skillid.

thanks for your reply and try. I spose its just sometimes easier to do it in code.

 

[McMerfy]

Try this one:

SELECT DISTINCT PE.COMMENCEDATE, SN.SKILLID, SN.TIMEPERIOD, TP.WEEKS
FROM PERSONEMPLOYMENT PE
  JOIN SKILLNEEDED SN ON (SN.EMPLOYMENTID = PE.EMPLOYMENTID)
  JOIN TIMEPERIOD TP ON (TP.TIMEPERIODID = SN.TIMEPERIODID)
  JOIN SKILL S ON (S.SKILLID = SN.SKILLID)AND
   (SN.SKILLID NOT IN (SELECT SA.SKILLID FROM SKILLATTEMPT SA
    WHERE (SA.PERSONID = PE.PERSONID)AND(SA.SKILLID = SN.SKLLID)AND
          (SA.RESULT = 'P')AND((SA.VALIDTO IS NULL)OR(SA.VALIDTO > '2/11/2003'))))
WHERE (PE.PERSONID = 1)AND
 (PE.COMMENCEDATE = (SELECT MAX(PE2.COMMENCEDATE) FROM PERSONEMPLOYMENT PE2
  WHERE PE2.PERSONID = PE.PERSONID))
ORDER BY NAME, WEEKS

--- markus

 

[Tracey]

ooooer that one returned nothing lol

cheers for the answer tho, of all the places i posted this you have been the only person to have a go.


like i say.. maybe sometimes its just easier to do what u want in code