mardi 27 janvier 2015

How to display multiple row records of similar ID's in single row?


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