CODEWITHSUNDEEP
sqlsql-serverjoin
Tugrul Uzel
Tugrul Uzel

Reputation: 175

SQL Server : removing duplicate column while joining tables

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.

Upvotes: 16

Views: 25542

Answers (3)

r.a.shehni
r.a.shehni

Reputation: 378

you have common columns on all tables so could use using(common_column) to remove duplicated columns.

SELECT * 
FROM table1
LEFT JOIN table2 using(Cust_ID)
LEFT JOIN table3 using(Cust_ID)
LEFT JOIN table4 using(Cust_ID)

I hop that useful.

Upvotes: 17

Saif
Saif

Reputation: 2689

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

enter image description here

Upvotes: 10

Gordon Linoff
Gordon Linoff

Reputation: 1270081

No, you cannot easily do what you want in SQL Server. In other databases, you can use the using clause.

One thing you can do is select the columns explicitly from all but the first table:

SELECT t1.*, . . .
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t1.Cust_ID = t2.Cust_ID LEFT JOIN
     table3
     ON t1.Cust_ID = table3.Cust_ID LEFT JOIN
     table4
     ON t1.Cust_ID = table4.Cust_ID;

Perhaps more important than the column issue, I changed the join conditions. You are using LEFT JOIN, so the first table is the "driving" table. When you say t2.Cust_ID = t3.Cust_Id, this returns true only when there was a match to table2. In general, you want to use the columns from table1, because it is the first one in the chain of LEFT JOINs.

Upvotes: 2

Related Questions