Click here to Skip to main content
15,889,595 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi please help in a sql query...
join on two table the structure is as follows

table 1
name       amt          month       year         id   
H12	1453098.00	  9	    2010	1458
H12	25739446.00	 10	    2010	1458


table 2
name       namt         month       year         id
H11	7600518.00	 10	    2010	1465
H12	239937.00	 10	    2010	1458


i wrote a join query :
SQL
select distinct t1.amt ,t2.namt, t1. id,t1.month from 
table1 t1 left outer join table2 t2 on t1.name=t2.name and t1.id=t2.id
it is resulting the rows 

amt                    namt            id       month
1453098.00	   239937.00	     1458	9
25739446.00	   239937.00	     1458	10

whereas the result should be
amt namt id month
1453098.00 null 1458 9
25739446.00 239937.00 1458 10
i.e the namt value for month 9 should be null
,pls correct the query
Posted
Updated 7-Sep-11 20:01pm
v4
Comments
Pradeep Shukla 7-Sep-11 22:05pm    
There is no column as "fieldbankacc" in table1 as per your description...why is the condition added with this column..this query should not work at all..
chitra81 8-Sep-11 1:46am    
thanks for looking, i have corrected the column name.
chitra81 8-Sep-11 2:33am    
the problem is not with just two tables, it comes when tables in joins increases, then only first join shows the right result.how can i perform left outer join on multiple tables with a single table

You are joining on name and id, which are the same for both those rows. Although you select month, you do not join on it. So, any t1.name that equals H12 and t1.id that equals 1458 will match to the same t2.namt, regardless of their differences. Try to include: t1.month = t2.month in the join.
 
Share this answer
 
Comments
chitra81 8-Sep-11 2:05am    
i need to show the result for two months that's why i have not added month in the condition .
JOAT-MON 8-Sep-11 12:54pm    
it is a left join so it will show all the months.
Use right outer join,currently it will show all the data from the left table and match from right.
 
Share this answer
 
Comments
chitra81 8-Sep-11 2:06am    
did that not working, i need to show the rows for two month from both the table.
left outer joins on table did the trick, although after involving months in it.thanks all for ur consideration
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900