2008/2/18 King C. Kwok <[EMAIL PROTECTED]>:
> table users
>
> id user_id user_name
> 1 M100001 Shirley
> 2 M100002 Bruce
> 3 M100003 Fred
> 4 M100004 Albert
> 5 M100005 Elizabeth
> 6 T100001 Helen
> 7 T100002 Tracy
> 8 T100003 Charles
> 9 T100004 Jack
> 10 T100005 Ann
>
> table job_records
>
> job_id submit_user_id submit_time fixed_user_id
> fixed_time
> SC12033214495468 T100003 1203321449 M100001 1203321763
> SC12033215980303 T100003 1203321598 M100001 1203321788
> SC12033216636547 T100002 1203321663 M100001 1203321796
> SC12033216729280 T100004 1203321672 M100003 1203321803
> SC12033216819810 T100005 1203321681 M100005 1203321809
> SC12033216898223 T100001 1203321689 M100003 1203321816
>
> How to select out data with below format?
> job_id submit_user_id submit_time fixed_user_id
> fixed_time
> SC12033214495468 Charles 1203321449 Shirley 1203321763
> SC12033215980303 Charles 1203321598 Shirley 1203321788
> SC12033216636547 Tracy 1203321663 Shirley 1203321796
> SC12033216729280 Jack 1203321672 Fred 1203321803
> SC12033216819810 Ann 1203321681 Elizabeth 1203321809
> SC12033216898223 Helen 1203321689 Fred 1203321816
>
>
> select t1.job_id, t2.user_name, t1.submit_time, t1.fixed_user_id,
> t1.fixed_time
> from job_records as t1 left join users as t2
> on (t1.submit_user_id = t2.user_id);
> The output is,
> job_id user_name submit_time fixed_user_id fixed_time
> SC12033214495468 Charles 1203321449 M100001 1203321763
> SC12033215980303 Charles 1203321598 M100001 1203321788
> SC12033216636547 Tracy 1203321663 M100001 1203321796
> SC12033216729280 Jack 1203321672 M100003 1203321803
> SC12033216819810 Ann 1203321681 M100005 1203321809
> SC12033216898223 Helen 1203321689 M100003 1203321816
>
> Thanks in advance!
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
First off it is usally a good idea to explain exactly what is not working
for you. The 'below format' is pretty ambiguous.
It looks like all you need to do in order to get what you want is to join on
the user table again and alias a few columns.
select
t1.job_id,
t2.user_name as `submit_user_id`,
t1.submit_time,
t3.user_name as `fixed_user_id`,
t1.fixed_time
from job_records as t1
left join users as t2 on t1.submit_user_id = t2.user_id
left join users as t3 on t1.fixed_user_id = t3.user_id;
Is that what you are going for? If not, then please explain in a bit better
detail what is problematic.
--
Rob Wultsch
