MySQL - Joining a Table on a Table Already Joined

Question

Here is a visual of the layout of the Tables, if you look below, my goal is to join wn_writing_number_cstm.id_c field to preps_contracted_reps.id.

To clarify, what you see here must stay the same, I need to keep the joins where they are, but in addition, grab the writing number for the Contracted Rep. (I need to get the title_c field.)

Here is my current code, I have tried multiple joins but can't seem to figure out the proper JOIN to get what I need. This statement gets everything I need except for the title_c field as it relates to the writing number of the rep.

EDIT: Once I added the extra JOIN statement with the alias, it now brings back nothing.

SELECT
leads.id,
leads.date_entered,
leads.date_modified,
leads.first_name,
leads.last_name,
leads.primary_address_state,
leads.converted,
leads.`status`,
leads.lead_source,
leads_cstm.territory_c,
leads_cstm.contact_status_c,
leads_cstm.campaign_id_c,
leads_cstm.industry_c,
leads_cstm.contract_type_c,
leads_cstm.recruiting_link_sent_c,
preps_contracted_reps.first_name AS rep_first_name,
preps_contracted_reps.last_name AS rep_last_name,
wn_writing_number_cstm.title_c
FROM
leads_wn_writing_number_1_c
JOIN wn_writing_number
ON leads_wn_writing_number_1_c.leads_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id 
JOIN leads_cstm
ON leads_wn_writing_number_1_c.leads_wn_writing_number_1leads_ida = leads_cstm.id_c 
RIGHT JOIN leads
ON leads.id = leads_cstm.id_c 
JOIN wn_writing_number_cstm
ON wn_writing_number.id = wn_writing_number_cstm.id_c 
JOIN preps_contracted_reps_wn_writing_number_1_c
ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number_cstm.wn_writing_number_id_c 
JOIN preps_contracted_reps
ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
JOIN preps_contracted_reps AS upline_table
ON upline_table.id = wn_writing_number_cstm.id_c
WHERE
leads.deleted = '0'
GROUP BY
leads.id

I also, tried doing something like this, but according to this post here: sql server - IN clause with multiple fields. You can't bring back multiple columns on the IN statement. This one gets me back the correct title_c. But I would also need all the fields I was selecting from above as it relates to the subquery below.

SELECT
preps_contracted_reps.first_name AS rep_first_name,
preps_contracted_reps.last_name AS rep_last_name,
wn_writing_number_cstm.title_c
FROM
preps_contracted_reps_wn_writing_number_1_c
JOIN wn_writing_number_cstm
ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number_cstm.id_c 
JOIN preps_contracted_reps
ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
WHERE
preps_contracted_reps.id IN 
(SELECT
preps_contracted_reps.id
FROM
leads_wn_writing_number_1_c
JOIN wn_writing_number
ON leads_wn_writing_number_1_c.leads_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id 
JOIN leads_cstm
ON leads_wn_writing_number_1_c.leads_wn_writing_number_1leads_ida = leads_cstm.id_c 
RIGHT JOIN leads
ON leads.id = leads_cstm.id_c 
JOIN wn_writing_number_cstm
ON wn_writing_number.id = wn_writing_number_cstm.id_c 
JOIN preps_contracted_reps_wn_writing_number_1_c
ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number_cstm.wn_writing_number_id_c 
JOIN preps_contracted_reps
ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
WHERE
leads.deleted = '0'
GROUP BY
leads.id)
GROUP BY
preps_contracted_reps.id

EDIT: I have updated this question to include a revised statement. The first Statement brings back nothing when I attempt to join them using an alias as suggested.

Answer 1

Using Pred's suggestion of aliases, I was able to figure out that I needed to alias more than 1 table. I needed to alias the relationship table again in addition to the table I wanted to retrieve data from.

SELECT
leads.id,
leads.date_entered,
leads.date_modified,
leads.first_name,
leads.last_name,
leads.primary_address_state,
leads.converted,
leads.`status`,
leads.lead_source,
leads_cstm.territory_c,
leads_cstm.contact_status_c,
leads_cstm.campaign_id_c,
leads_cstm.industry_c,
leads_cstm.contract_type_c,
leads_cstm.recruiting_link_sent_c,
preps_contracted_reps.first_name AS rep_first_name,
preps_contracted_reps.last_name AS rep_last_name,
upline_table.title_c
FROM
leads_wn_writing_number_1_c
JOIN wn_writing_number
ON leads_wn_writing_number_1_c.leads_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id 
JOIN leads_cstm
ON leads_wn_writing_number_1_c.leads_wn_writing_number_1leads_ida = leads_cstm.id_c 
RIGHT JOIN leads
ON leads.id = leads_cstm.id_c 
JOIN wn_writing_number_cstm
ON wn_writing_number.id = wn_writing_number_cstm.id_c 
JOIN preps_contracted_reps_wn_writing_number_1_c
ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number_cstm.wn_writing_number_id_c 
JOIN preps_contracted_reps
ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id 
JOIN preps_contracted_reps_wn_writing_number_1_c AS upline_relationship
ON preps_contracted_reps.id = upline_relationship.preps_cont9effed_reps_ida 
JOIN wn_writing_number_cstm AS upline_table
ON upline_relationship.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = upline_table.id_c
WHERE
leads.deleted = '0'
GROUP BY
leads.id