I have two tables user and work. Work table has multiple user's work experience.I have to retrieve most recent 2 companies on the basis of start date.
Conditions :
1.If Work Table has multiple user's Id's records, retrieve recent 2 records and show in single row (e.g. User_id = 1)
2.If Work Table has single record is most recent companies record and second record should be "NULL"(e.g. user_id = 3).
user
user_id First_name Last_name
1 AAA BBB
2 PPP QQQ
3 SSS RRR
work
user_id recent_company position start_year end_year
1 ABC CCC 2014 2015
1 PQR DDD 2013 2014
1 MNO EEE 2012 2013
1 MNO EEE 0000 0000
2 XYZ TTT 2008 2009
2 IJK MMM 2005 2008
3 QRS ZZZ 2001 2002
I've tried for most recent company record 1 :
select u.user_id,u.first_name,u.last_name,uw.recent_company1,uw.position1,uw.start_year,uw.end_year from muser u
left join
(SELECT user_id,recent_company,position,MAX(start_year) as start_year,end_year
FROM work
group by user_id
order by user_id) uw ON u.user_id =uw.user_id
Result of my query for 1st recent company on start year basis:
user_id First_name Last_name recent_company1
1 AAA BBB ABC
2 PPP QQQ XYZ
3 SSS RRR QRS
position1 start_year1 end_year1
CCC 2014 2015
TTT 2008 2009
ZZZ 2001 2002
Required Output for both 1st and 2nd recent company on start_year basis:
user_id First_name Last_name recent_company1 position1
1 AAA BBB ABC CCC
2 PPP QQQ XYZ TTT
3 SSS RRR QRS ZZZ
start_year1 end_year1
2014 2015
2008 2009
2001 2002
recent_company2 position2 start_year2 end_year2
PQR DDD 2013 2014
IJK MMM 2005 2008
NULL NULL NULL NULL
Aucun commentaire:
Enregistrer un commentaire