SQL Server : removing duplicate column while joining tables

Question

I have 4 tables with one column is common on all tables. Is there a way to create a view where I can join all tables by same column where I see the common column only once.

Let's say I have table1

Cust ID | Order ID | Product_Name

Table2

Cust_ID | Cust_Name | Cust_Address

Table3

Cust_ID | Cust_Acc | Acc_Type

Table4

Cust_ID | Contact_Phone | Cust_Total_Ord

Here is the code I use to join tables;

SELECT * 
FROM table1
LEFT JOIN table2 ON table1.Cust_ID = table2.Cust_ID
LEFT JOIN table3 ON table2.Cust_ID = table3.Cust_ID
LEFT JOIN table4 ON table3.Cust_ID = table4.Cust_ID

I get all tables joined by I see Cust_ID from each table as below;

Cust ID| Order ID|Product_Name| Cust_ID| Cust_Name|Cust_Address| Cust_ID| Cust_Acc| Acc_Type|Cust_ID|Contact_Phone|Cust_Total_Ord

Is there a way to remove duplicate Cust_ID columns or do I need to write each column name in the SELECT? I have more than 50 columns in total so will be difficult to write all.

Sorry if it is a really dumb question, I have checked previous similar questions but couldn't figure out and thanks for help.

Answer 1

you need to select columns from three tables first and then make inner join like below

select 
  t1.cust_id, t1.col1, t1.col2, 
  t2.col1_table2, t2.col2_table2, 
  t3.col1_table3, t3.col2_table3
from
 table1 t1 
inner join
 table2 t2 on t1.cust_id = t2.cust_id
join table3 t3 on t1.cust_id = t3.cust_id

Result as shown in below image