Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 26 Nov 2003 @ 19:19:48 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Inner Join Problems
 
From:   Ferry, Craig

Has anyone experienced this problem. I have already opened an incident with NCR, but wanted to through this out to see if anyone else has encountered this problem.

I would have expected all 3 of these queries to return 1 row for branch 1111 since there is only 1 branch 1111 in the branch_tbl table.

Here is a summary of my 3 queries.


Query 1

select b.group_id,
         sd.branch_id,
         sum(sd.sales_amount)
from dss_tables.sales_detail sd
inner join dss_tables.branch_tbl b
on sd.branch_id = b.branch_id
group by 1,2

This is an inner join query not specifying anything in the where clause. This query returned 28 rows for branch 1111. If you add the sales_amount up for all 28 rows, it totals to the amount in query 2 and 3.


Query2

select b.group_id,
         sd.branch_id,
         sum(sd.sales_amount)
from dss_tables.sales_detail sd
left outer join dss_tables.branch_tbl b
on sd.branch_id = b.branch_id
group by 1,2

This is the same as query 1 except using a left outer join. This retured 1 row for branch 1111.


Query 3.

select b.group_id,
         sd.branch_id,
         sum(sd.sales_amount)
from dss_tables.sales_detail sd
inner join dss_tables.branch_tbl b
on sd.branch_id = b.branch_id
where sd.branch_id = 1111
group by 1,2

This is an inner join query which specified branch_id = 1111 in the where clause. This also returned 1 row.


TIA

Craig



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016