CODEWITHSUNDEEP
sqldatabaseoracle-databaseleft-joinoracle9i
kumar
kumar

Reputation: 691

leftouter join in sql

I have two tables initially in the application. They are as

user                       
------------------------      
user_id                      
column1                         
column2
..

Columnn

user_account_info

user_id                   
account_number (can have multiple a/c number on single user_id)        
column1                      
column2          
..            
Columnn

the query is like 

SELECT u.column1, uai.column1, uai.Keycolumn2
        FROM user u
        LEFT OUTER JOIN user_account_info uai on u.user_id  = uai.user_id

But later a new table user_account_map is added and user_account_info is changed

user

user_id                 
column1                                        
column2             
..               
Columnn

user_account_info

account_number (can have multiple a/c number on single user_id)                       
column1                  
column2               
..               
Columnn

user_account_map

user_id                      
account_number (can have multiple a/c number on single user_id)                
column1              
column2

How to rewrite the query to match the output that of the previous query. Is it ok to write like 

SELECT u.column1, uai.column1, uai.Keycolumn2
FROM user u
LEFT OUTER JOIN user_account_map uam on u.user_id  = uam.user_id
LEFT OUTER JOIN user_account_info uai on u.account_number  = uai.account_number

Upvotes: 0

Views: 114

Answers (2)

kumar
kumar

Reputation: 691

My query has the answer for itself. The query, for which i have asked, whether it is correct or not, is the correct one.
That is,

SELECT u.column1, uai.column1, uai.Keycolumn2
FROM user u
LEFT OUTER JOIN user_account_map uam on u.user_id  = uam.user_id
LEFT OUTER JOIN user_account_info uai on u.account_number  = uai.account_number

Upvotes: 1

aleroot
aleroot

Reputation: 72636

From what I can understand from your fuzzy question, I think that you only need to JOIN the three tables and since you have multiple user_id in the table user_account_map you will need to GROUP BY that field :

SELECT u.column1, uai.column1, uai.Keycolumn2, MAX(account_number) AS accNo
FROM user u 
LEFT OUTER JOIN user_account_info uai USING(user_id)
LEFT OUTER JOIN user_account_map uma  USING(user_id)
GROUP BY uma.user_id

Or otherwise simply JOIN the two tables containing account_number field with a double condition :

SELECT u.column1, uai.column1, uai.Keycolumn2
FROM user u 
LEFT OUTER JOIN user_account_info uai USING(user_id)
LEFT OUTER JOIN user_account_map uma ON uma.account_number = uai.account_number AND uma.user_id = u.user_id

Upvotes: 2

Related Questions