SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 changing from implicit join to explicit join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anjali5
Yak Posting Veteran

92 Posts

Posted - 12/24/2013 :  15:31:15  Show Profile  Reply with Quote
Hi All,

we are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.

Below is my Implict join query



INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow      
 
WHERE    
		currow.rf0_row_no = prevrow.rf0_row_no + 1   
		and currow.rf0_key_id_no = prevrow.rf0_key_id_no

DECLARE @countTemp  int
select @countTemp = count(*) from #RIF_TEMP1
PRINT @countTemp 

and below is explict join query

INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow      
 ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
WHERE    
		   
		 currow.rf0_key_id_no = prevrow.rf0_key_id_no

DECLARE @countTemp  int
select @countTemp = count(*) from #RIF_TEMP1
PRINT @countTemp 


the count returned from both the queries is different.

I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.

Thanks.

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 12/24/2013 :  15:56:58  Show Profile  Reply with Quote
Hard to tell without sample data, but your WHERE clause on the second query (LEFT OUTER JOIN) is making it an INNER JOIN. Try moving the predicate onto the join clause maybe?
ON currow.rf0_row_no = prevrow.rf0_row_no + 1
 AND currow.rf0_key_id_no = prevrow.rf0_key_id_no
Go to Top of Page

anjali5
Yak Posting Veteran

92 Posts

Posted - 12/24/2013 :  16:01:31  Show Profile  Reply with Quote
Thank you!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/25/2013 :  00:55:50  Show Profile  Reply with Quote
see this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

anjali5
Yak Posting Veteran

92 Posts

Posted - 12/26/2013 :  14:10:53  Show Profile  Reply with Quote
I read the article that you posted here. I did the same thing in my another query and still getting wrong results. Below is the query that has implicit join


insert #Status(name, sn, key, sname, sn, pn, mfr_date)
select  b.abbr,  b.sn,  b.key_id_no, a.name_abbr, a.sn, a.pn_mk_mod, a.key_id_no 
from STATUS a ,STATUS b  

WHERE
 a.key *= b.key_id_no  AND
 a.name = @in_sect  AND
 b.name = 'TEST'



I need to put the explicit join in below



insert #Status(name, sn, key, sname, sn, pn, mfr_date)
select  b.abbr,  b.sn,  b.key_id_no, a.name_abbr, a.sn, a.pn_mk_mod, a.key_id_no 
from STATUS a LEFT JOIN STATUS b  
  ON(a.key = b.key_id_no AND a.name = @in_sect  AND  b.name = 'TEST')




I am not sure what am I doing wrong

Edited by - anjali5 on 12/26/2013 14:27:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/26/2013 :  14:18:30  Show Profile  Reply with Quote
Nope the posted queries are not equivalent
the first query is doing an INNER join so corresponding explicit join query should be this

insert #Status(name, sn, key, sname, sn, pn, mfr_date)
select  b.abbr,  b.sn,  b.key_id_no, a.name_abbr, a.sn, a.pn_mk_mod, a.key_id_no 
from STATUS a INNER JOIN STATUS b  
  ON a.key = b.key_id_no AND a.name = @in_sect  AND  b.name = 'TEST'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

anjali5
Yak Posting Veteran

92 Posts

Posted - 12/26/2013 :  14:28:12  Show Profile  Reply with Quote
sorry, I forgot to put the *= for the original query. I just edited my post.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/28/2013 :  01:58:25  Show Profile  Reply with Quote

can you post some data and explain the issue? without which i dont think we will be able to suggest anything further.
post sample data and output out of the two queries and also your required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000