i was waiting a bit for a more sage response, but ...
could someone help me with my query please?
 
 I have the following tables
 
/* snip ... */
 
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)
 
i think the problem with the DISTINCT might not be "all the joins", but instead that that different time periods make rows with the same skill different so they both show up.  
 
in similar situations i've handled this in 2 different ways.
 
1) use GROUP BY on all the distinct columns with MIN() on the time period column.  
 
2) write a SP (in IB or Oracle where SPs can return rowsets) or a Function (in MSSQL after 7.0) that does the select with ORDER BY on the time period , fetchs one (the first) row into the return arguments of the SP and then returns.
 
neither very elegant i'm afraid, but maybe you will find it useful
 
 
 
 
 

Reply via email to