locked
Getting single query from duplicate rows in sql server 2008 RRS feed

  • Question

  • User1804579801 posted

    I have 3 tables registrations where candidates register, Table_ApplicationsInfo were candidates applied positionId details are stored.Education table  were qualification details are 

    stored every candidate have multiple qualifications like bachelors,master .Now i want single highest qualification having min priority 

    Select * from(Select Row_number() over(partition by t2.ApplicationId,t1.RegistrationId, t1.GivenName,
    t1.Email, t1.Nationality, t1.CellPhone, t1.ExperienceYear, t1.Title,t.Qualification, t2.AppliedPositionId,DATEDIFF(hour,t1.DOB,GETDATE())/8766 Order by RegistrationId ASC) as RowNo,t2.ApplicationId,t1.RegistrationId, t1.GivenName,
    t1.Email, t1.Nationality, t1.CellPhone, t1.ExperienceYear, t1.Title,t.Qualification, t2.AppliedPositionId,
    DATEDIFF(hour,t1.DOB,GETDATE())/8766 AS AGE FROM Table_RegistrationInfo AS t1 INNER JOIN
    Table_ApplicationsInfo AS t2 ON t1.Email = t2.AppliedCandidateId INNER JOIN
    Table_EducationInfo t on t.Email=t1.Email
    inner join (select email, min(Priority) AS Priority from Table_EducationInfo group by Email) tm on t.Email = tm.Email and t.Priority = tm.Priority
    WHERE (t2.AppliedPositionId = @PositionId) and (t2.Status=@Status )) as Tab
    Where Tab.RowNo between (@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) -

    when i execut this query i get multiple records as one candidate have multiple qualification .Please suggest   

    Thursday, May 9, 2019 6:47 AM

All replies

  • User753101303 posted

    Hi,

    And you have only a single priority value for each email id (ie you have a unique (email,priority) index ? Not 100% sure but for clarity or designing the query I would try first :

    left join (select email, min(Priority) AS Priority from Table_EducationInfo group by Email) tm on t1.Email = tm.Email -- See first the best qualification, do you have the expected row count with that ?

    And if ok I would add then :
    LEFT JOIN Table_EducationInfo t on t.Email=tm.Email and t.Priority = tm.Priority -- and select only that row

    It WON'T work if you can have the same priority value multiple times for a single mail id (not sure also if you always have at least qualification and what you want to do in this case).

    In short the basic idea is to build your query step by step to see where your join starts to be wrong.

    Edit: not directly related but if email is an the actual mail it is perhaps not a very good choice (it doesn't change often but it coiuld change ?)

    Thursday, May 9, 2019 5:07 PM