CODEWITHSUNDEEP
sqldatabase
user3532090
user3532090

Reputation: 31

Join 3 tables such that for its unique records, data can be retrieved from a third table

I have 3 tables -
table 1 (t1) having mulitple instances of accountnos table 2 (t2) having unique instances of accountno and its customerno (t2 has additional accounts in comparison with t1) table 3 (t3) having details of the customerno

I want to join the three tables such that for unique instances of accountnos from t1, I can retrieve customer details from t3. In case customer details dont exist in t3, I still want the accountno from t1.

Example: 

t1.accountno  
x  
x  
m  

t2.accountno t2.customerno  
x custid1  
y custid2  
z custid3  

t3.customerno t3.customername  
custid1 John  
custid2 Roy  

expected o/p  
t1.accountno t2.customerno t3.customername  
x custid1 John

Upvotes: 0

Views: 27

Answers (1)

Mittal Patel
Mittal Patel

Reputation: 2762

It is simple inner join and group by to avoid the duplicate values

SELECT t1.accountno, t2.customerno, t3.customername FROM t1
    JOIN t2 cn ON t1.accountno = t2.accountno 
    JOIN t3 cn ON t2.customerno = t3.customerno 
Group By t1.accountno, t2.customerno, t3.customername

Upvotes: 1

Related Questions