Is it possible to join 1 column of a table to 2 columns of a table?

Is it possible to join a table twice with the same table but multiple columns


eg. 

Select 

T1.Name, T2.Name, T3.NickName

from Table_1 T1

JOIN Table_2 T2

ON T2.Name = T1. Name


JOIN Table_2 T3

ON T3.Name = T1.Nickname

I don't have the examples since I'm just trying to help someone.


Note: It works when you use the test query but in the site itself it will throw an exception, defining that it has a duplicate column value.


Kind Regards,

Wil

mvp_badge
MVP
Solution

HI Wil,

Instead of * you need to define column name based on alias .

like T1.Name as 'Name',T1.NinckName as 'NickName', T2.name as 'Name1'


hope this will help you.

Regards

Rahul

oh sorry about the "select *", the columns are actually unique, I'll edit the question


mvp_badge
MVP

have you tried this way -

T1.Name as 'Name',T1.NinckName as 'NickName', T2.name as 'Name1' 

provide column name alias


Here is the query:

SELECT R.[rrf_no]

, Acc.[dropdown_value]

, Cl.[client]

, Engage.[dropdown_value]

, R.[type_of_demand]

, R.[target_start_date]

, Cn.[target_end_date_sc_and_pb]

, Cn.[submitted_datetime]

, concat(AD.[first_name]," ", AD.[last_name])

, Cn.[last_modified_by_datetime]

, Cn_Status.[new_status]

, Cn_Status.[new_status_remarks]

, AD.[platform]

, AD.[platform_source]

, AD.[referred_by_name]

, AD.[referrer_type]

, AD.[referrer_account]

, AD.[birthday]

, AD.[marital_status]

, AD.[primary_contact_number]

, AD.[primary_email]

, AD.[address]

, AD.[present_address]

, AD.[country]

, AD.[passport_available]

, AD.[type_of_passport]

, AD.[with_us_visa]

, AD.[education]

, AD.[current_previous_company]

, AD.[current_previous_position]

, AD.[current_employment_type]

, AD.[industry]

, AD.[current_monthly_salary]

, AD.[months_guaranteed]

, AD.[salary_effectivity_date]

, AD.[years_of_experience]

, AD.[notice_period]

, C.[business_tower]

, C.[skill_set]

, C.[level]

, Cn.[pay_rate]

, Cn.[bill_rate]

, Cn.[interviewer_internal_screening]

, Cn.[interviewer_client]

, Cn.[submitted_by_name]

, C.[category]

, Cn.[hiring_points]

, Cn.[date_jo_signed]

, Cn.[onboard_date]

, Cn.[working_days_sjo_vs_ob]

, Cn.[remarks]

, Cn.[work_schedule]

, R.[location]

, Cn.[new_or_replacement_of]

, Cn.[employment_type_with_gsi]

, Cn.[benefit_level]

, Cn.[medical_exam_schedule]

, Cn_Status.[created_datetime]



FROM {applicant_details} AD




JOIN {candidates} Cn

ON AD.[applicant_id] = Cn.[applicant_id]



JOIN {rrfs} R

ON Cn.[rrf] = R.[id]



JOIN {candidate_status_logs} Cn_Status

ON Cn.[id] = Cn_Status.[candidate]



JOIN {clients} Cl

ON R.[client_id] = Cl.[id]



JOIN {categories} C

ON R.[category_id] = C.[id]



JOIN {dropdown_values} Acc

ON Acc.[field_name] = R.[account_field_name]


JOIN {dropdown_values} Engage

ON Engage.[field_name] = R.[engagement_model_field_name]





WHERE (

(DATE(Cn_Status.[created_datetime]) >= @DateFrom)

AND (DATE(Cn_Status.[created_datetime]) <= @DateTo)

AND Cn.[is_deleted] = FALSE )




GROUP BY 

Cn.[id], AD.[applicant_id], R.[id], Cn_Status.[created_datetime]


ORDER BY Cn_Status.[created_datetime] DESC


--group by R.[id], AD.[applicant_id], Cn.[applicant_id]

Champion

Hi Wii,

it is possible to  join a table twice with the same table but multiple columns  

As Rahul's answer,  you have to define an alias name and also define the correct output structure.


Kind regards,

Here is the query:

SELECT R.[rrf_no]

, Acc.[dropdown_value]

, Cl.[client]

, Engage.[dropdown_value]

, R.[type_of_demand]

, R.[target_start_date]

, Cn.[target_end_date_sc_and_pb]

, Cn.[submitted_datetime]

, concat(AD.[first_name]," ", AD.[last_name])

, Cn.[last_modified_by_datetime]

, Cn_Status.[new_status]

, Cn_Status.[new_status_remarks]

, AD.[platform]

, AD.[platform_source]

, AD.[referred_by_name]

, AD.[referrer_type]

, AD.[referrer_account]

, AD.[birthday]

, AD.[marital_status]

, AD.[primary_contact_number]

, AD.[primary_email]

, AD.[address]

, AD.[present_address]

, AD.[country]

, AD.[passport_available]

, AD.[type_of_passport]

, AD.[with_us_visa]

, AD.[education]

, AD.[current_previous_company]

, AD.[current_previous_position]

, AD.[current_employment_type]

, AD.[industry]

, AD.[current_monthly_salary]

, AD.[months_guaranteed]

, AD.[salary_effectivity_date]

, AD.[years_of_experience]

, AD.[notice_period]

, C.[business_tower]

, C.[skill_set]

, C.[level]

, Cn.[pay_rate]

, Cn.[bill_rate]

, Cn.[interviewer_internal_screening]

, Cn.[interviewer_client]

, Cn.[submitted_by_name]

, C.[category]

, Cn.[hiring_points]

, Cn.[date_jo_signed]

, Cn.[onboard_date]

, Cn.[working_days_sjo_vs_ob]

, Cn.[remarks]

, Cn.[work_schedule]

, R.[location]

, Cn.[new_or_replacement_of]

, Cn.[employment_type_with_gsi]

, Cn.[benefit_level]

, Cn.[medical_exam_schedule]

, Cn_Status.[created_datetime]



FROM {applicant_details} AD




JOIN {candidates} Cn

ON AD.[applicant_id] = Cn.[applicant_id]



JOIN {rrfs} R

ON Cn.[rrf] = R.[id]



JOIN {candidate_status_logs} Cn_Status

ON Cn.[id] = Cn_Status.[candidate]



JOIN {clients} Cl

ON R.[client_id] = Cl.[id]



JOIN {categories} C

ON R.[category_id] = C.[id]



JOIN {dropdown_values} Acc

ON Acc.[field_name] = R.[account_field_name]


JOIN {dropdown_values} Engage

ON Engage.[field_name] = R.[engagement_model_field_name]





WHERE (

(DATE(Cn_Status.[created_datetime]) >= @DateFrom)

AND (DATE(Cn_Status.[created_datetime]) <= @DateTo)

AND Cn.[is_deleted] = FALSE )




GROUP BY 

Cn.[id], AD.[applicant_id], R.[id], Cn_Status.[created_datetime]


ORDER BY Cn_Status.[created_datetime] DESC


--group by R.[id], AD.[applicant_id], Cn.[applicant_id]

Yup, Rahul was correct. Thank you

I'll edit the question again, I got the query from the person I'm helping

Champion

Hi Wiil,

I understand it, What means by "It works when you use the test query"?

Is that mean in the server studio SQL designer? Like below. If so it's very weird, because it should have the same behavior with runtime.

Kind regards,

We also check the service center for the error:


I think I found the problem. it's the acc.dropdown_value and engage.dropdown_value from select

please confirm


Thank you guys. Rahul was correct. I thought the query was all unique until my colleague gave it to me.


You guys are always reliable. Thank you


Kind Regards,

Wil

mvp_badge
MVP

Yes Wil,

 you got it, Glad to help you.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.