SQL Condition based dataset

I have a SQL Server database that I did not design. The employees have degrees, licensures and credentials stored in a few different tables. I have written the query to join all of this information together so I can see an over all result of what the data looks like. I have been asked to create a view for this data that returns only the highest degree they have obtained and the two highest certifications. The problem is, as it is pre existing data, there is no hierarchy built into the data. All of the degrees and certifications are simply stored as a string associated with their employee number. The first logical step was to create an adjacency list(I believe this is the correct term). For example 'MD' is the highest degree you can obtain in our list. So I have given that the "ranking" of 1. The next lower degree is "ranked" as 2. and so forth. I can join on the text field that contains these and return their associated rank. The problem I am having is returning only the two highest based on this ranking. If the employee has multiple degrees or certifications they are listed on a second or third row. From a logical standpoint, I need to group the employee ID, First name and Last name. Then some how concatenate the degrees, certifications and licensures based on the "ranking" I created for them. It is not a true hierarchy in the way that I am thinking about it because I only need to know the highest two and not necessarily the relationship between t开发者_Python百科he results.

Another potential caveat is that the database must remain in SQL Server 2000 compatibility mode.

Any help that can be given would be much appreciated. Thank you.

select a.EduRank as 'Licensure Rank',
   b.EduRank as 'Degree Rank',
   EmpComp.EecEmpNo, 
  EmpPers.EepNameFirst, 
   EmpPers.EepNameLast, 
   RTRIM(EmpEduc.EfeLevel),
   RTRIM(EmpLicns.ElcLicenseID),
   a.EduType,
   b.EduType
       from empcomp 
      join EmpPers on empcomp.eeceeid = EmpPers.eepEEID
      join EmpEduc on empcomp.Eeceeid = EmpEduc.EfeEEID
      join EmpLicns on empcomp.eeceeid = EmpLicns.ElcEEID
      join yvDegreeRanks a on a.EduCode = EmpLicns.ElcLicenseID
      join yvDegreeRanks b on b.EduCode = EmpEduc.EfeLevel

I think I can see what your problem is - however I'm not sure. Joining the tables together has given you "double rows". The "quick-and-dirty" way to solve this query, would be to use Subqueries other than Joins. Doing so, you can select only the TOP 1 Degree, and TOP 2 certifications.

EDIT : Can you try this query ?

SELECT *
FROM employSELECT tblLicensures.EduRank as 'Licensure Rank',
    tblDegrees.EduRank as 'Degree Rank',
    EmpComp.EecEmpNo, 
    EmpPers.EepNameFirst, 
    EmpPers.EepNameLast, 
    RTRIM(tblDegrees.EfeLevel),
    RTRIM(tblLicensures.ElcLicenseID),
    tblLicensures.EduType,
    tblDegrees.EduType
FROM EmpComp
    LEFT OUTER JOIN EmpPers ON empcom.eeceeid = EmpPers.eepEEID
    LEFT OUTER JOIN
        -- Select TOP 2 Licensure Ranks
        (
            SELECT TOP 2 a.EduType, a.EduRank, EmpLicns.ElcEEID
            FROM yvDegreeRanks a 
                INNER JOIN EmpLicns on a.EduCode = EmpLicns.ElcLicenseID
            WHERE EmpLincs.ElcEEID = empcomp.eeceeid
            ORDER BY a.EduRank ASC
        ) AS tblLicensures ON tblLicensures.ElcEEID = empcomp.Eeceeid
    LEFT OUTER JOIN 
        -- SELECT TOP 1 Degree
        (
            SELECT TOP 1 b.EduType, b.EduRank, EmpEduc.EfeEEID, EmpEduc.EfeLevel
            FROM yvDegreeRanks b 
                INNER JOIN EmpEduc on b.EduCode = EmpEduc.EfeLevel
            WHERE EmpEduc.EfeEEID = empcomp.Eeceeid
            ORDER BY b.EduRank ASC
        ) AS tblDegrees ON tblDegrees.EfeEEID = empcomp.Eeceeid

This is not the most elegant solution, but hopefully it will at least help you out in some way.

create table #dataset (
licensurerank [datatype],
degreerank [datatype],
employeeid [datatype],
firstname varchar,
lastname varchar,
efeLevel  [datatype],
elclicenseid [datatype],
edutype1 [datatype],
edutype2 [datatype]
)

select distinct identity(int,1,1) [ID], EecEmpNo into #employeeList from EmpComp

declare
    @count int,
    @rows int,
    @employeeNo int

select * from #employeeList
set @rows = @@rowcount
set @count = 1

while @count <= @ROWS
    begin

select @employeeNo = EecEmpNo from #employeeList where id = @count

        insert into #dataset
        select top 2 a.EduRank as 'Licensure Rank',
           b.EduRank as 'Degree Rank',
           EmpComp.EecEmpNo, 
          EmpPers.EepNameFirst, 
           EmpPers.EepNameLast, 
           RTRIM(EmpEduc.EfeLevel),
           RTRIM(EmpLicns.ElcLicenseID),
           a.EduType,
           b.EduType
               from empcomp 
              join EmpPers on empcomp.eeceeid = EmpPers.eepEEID
              join EmpEduc on empcomp.Eeceeid = EmpEduc.EfeEEID
              join EmpLicns on empcomp.eeceeid = EmpLicns.ElcEEID
              join yvDegreeRanks a on a.EduCode = EmpLicns.ElcLicenseID
              join yvDegreeRanks b on b.EduCode = EmpEduc.EfeLevel
            where EmpComp.EecEmpNo = @employeeNo

    set @count = @count + 1
end

Have tables for employees, types of degrees (including a rank), types of certs (including a rank), and join tables employees_degrees and employees_certs. [It might be better to put degrees and certs in one table with a flag is_degree, if all their other fields are the same.] You can extract the existing string values and replace them with FK ids into the degree and cert tables.

The query itself is harder, because PARTITION BY is not available in SQL Server 2000 (according to Google). UW's answer has at least two problems: you need LEFT JOINs because not all employees have degrees and certs, and there is no ORDER BY to show what you want to take the best of. TOP 2 subqueries are particularly difficult to use in this context. So for that, I can't yet give an answer.