I have two tables Table A and Table B with many-to-one relationship, i.e for every record in Table A, there may be one or multiple rows in Table B. I am Left Outer joining Table A with Table B on columns : A.Employee_Id = B.Employee_Id.
For example, for Employee 1 in Table A, there may be two rows in Table B: first row Event_ID = "R" and second row Event_Id= "S". For Employee 2, there may be only one row with Event_Id ="R".
So basically, I want the rows in Table B to be sorted for every employee based on column value of Event_Id in the order "F", "S" and "R" and then pick the first row from this order, to be joined with Table A.
And I want only one record to be returned in the result for every employee. (Of course, NULL values if there are no records).
Please suggest how can this be done in an optimised way.
Table A Columns:
Employee_Id, First_Name, Last_Name
Table B Columns:
Employee_Id, Event_Id, Event_Comment
DB with row_number()
select a.Employee_Id, a.First_Name, a.Last_Name,
b.Event_Id, b.Event_Comment
from TableA a
left join
(select Employee_Id, Event_Id, Event_Comment,
row_number() over(partition by Employee_Id
order by case Event_ID
when 'F' then 1
when 'S' then 2
when 'R' then 3 end) as rw
) b
on a.Employee_Id = b.Employee_Id
where rw is null or rw = 1;
Ansi SQL (but there still may be several rows for each employer - if tableB has the same EventIds for one employer):
select a.Employee_Id, a.First_Name, a.Last_Name,
tmp.Event_Id, tmp.Event_Comment
from
tableA a
left join
(select Employee_Id, Event_Id, Event_Comment
from TableB b
join (select Employee_Id, min(case Event_ID
when 'F' then 1
when 'S' then 2
when 'R' then 3 end) as min_event
from TableB group by Employee_Id
) t
on t.Employee_Id = b.Employee_Id
and t.min_event = case b.Event_ID
when 'F' then 1
when 'S' then 2
when 'R' then 3 end
) tmp
on a.Employee_Id = tmp.Employee_Id;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments