Use a qry result in a new qry

[Adamhumbug]

Hi,

I am running a query in which the result set contains the ids of 2 users whose names are stored in the user table.

I want to get both of their names but not sure how i would go about this.

select a.job_manager_id, a.job_head_chef_id,a.job_id, a.job_eventname, a.job_date, a.job_end_date, b.customer_companyname, c.venue_name
from ssm_job a
inner join ssm_customer b on a.job_customer_id=b.customer_id
inner join ssm_venue c on a.job_venue_id = c.venue_id

Give me the results

2    9    35    Chelsea's Event    2019-12-23    2019-12-27    Cavuto, John A    A Corporation    

 

both the 2 and the 9 in the results refer to users.

In the users table (ssm_users) they have user_firstname and user_lastname

How can i return both of their full names here.

Happy to provide more structure if required.

Kind Regards

 

Adam

[Barand]

Join twice to the user table with different aliases

select a.job_manager_id
     , jm.username as manager
     , a.job_head_chef_id
     , hc.username as chef
FROM ssm.job a
     JOIN user jm ON jm.user_id = a.job_manager_id
     JOIN user hc ON hc.user_id = a.head_chef_id

 

[Adamhumbug]

7 minutes ago, Barand said:

Join twice to the user table with different aliases

select a.job_manager_id
     , jm.username as manager
     , a.job_head_chef_id
     , hc.username as chef
FROM ssm.job a
     JOIN user jm ON jm.user_id = a.job_manager_id
     JOIN user hc ON hc.user_id = a.head_chef_id

 

Thank you for this, i took your advice and used the following to get what i needed.

select a.job_id, a.job_eventname, a.job_date, a.job_end_date, b.customer_companyname, c.venue_name, d.user_firstname as managerf, d.user_lastname as managerl, e.user_firstname as cheff, e.user_lastname as chefl
from ssm_job a
inner join ssm_customer b on a.job_customer_id=b.customer_id
inner join ssm_venue c on a.job_venue_id = c.venue_id
left join ssm_user d on a.job_manager_id = d.user_id
left join ssm_user e on a.job_head_chef_id = e.user_id

 

[Barand]

HINT: I am all for 1 or 2 character aliases for tables but you would make your queries more readable with more meaningful aliases for the tables than just a, b, c, d, e

EG

• ssm_job j
• ssm_venue v
• ssm_customer c

[Adamhumbug]

7 minutes ago, Barand said:

HINT: I am all for 1 or 2 character aliases for tables but you would make your queries more readable with more meaningful aliases for the tables than just a, b, c, d, e

EG

• ssm_job j
• ssm_venue v
• ssm_customer c

I didnt think about that but it totally makes sense - Thanks again