January 20, 2009 at 3:13 pm
Tell me the difference in these two statements. The first give me back 2174 rows and the second give me like 827. What am I missing? I know the first is just looking at one table, but I need the two table join b/c of the 3rd statement which is an update.
1)
select HBG.dbo.IMINVLOC_SQL.item_no,HBG.dbo.IMINVLOC_SQL.std_cost,HBG.dbo.IMINVLOC_SQL.loc
from HBG.dbo.IMINVLOC_SQL --join FT.dbo.Std_Cost_Compare on HBG.dbo.IMINVLOC_SQL.item_no+HBG.dbo.IMINVLOC_SQL.loc=
--FT.dbo.Std_Cost_Compare.HBG_item_no+FT.dbo.Std_Cost_Compare.HBG_loc
where HBG.dbo.IMINVLOC_SQL.item_no in (select rtrim(ltrim(hbg_item_no))
from ft.dbo.Std_Cost_Compare)
2)
select HBG.dbo.IMINVLOC_SQL.item_no,HBG.dbo.IMINVLOC_SQL.std_cost,HBG.dbo.IMINVLOC_SQL.loc
from HBG.dbo.IMINVLOC_SQL join FT.dbo.Std_Cost_Compare on HBG.dbo.IMINVLOC_SQL.item_no+HBG.dbo.IMINVLOC_SQL.loc=
FT.dbo.Std_Cost_Compare.HBG_item_no+FT.dbo.Std_Cost_Compare.HBG_loc
where HBG.dbo.IMINVLOC_SQL.item_no in (select rtrim(ltrim(hbg_item_no))
from ft.dbo.Std_Cost_Compare)
3)
--Update HBG.IMINVLOC_SQL.std_cost table from Turnkey.IMINVLOC_SQL table
begin tran update HBG.dbo.IMINVLOC_SQL
set HBG.dbo.IMINVLOC_SQL.std_cost = (FT.dbo.Std_Cost_Compare.HBG_std_cost)
from HBG.dbo.IMINVLOC_SQL join FT.dbo.Std_Cost_Compare on HBG.dbo.IMINVLOC_SQL.item_no+HBG.dbo.IMINVLOC_SQL.loc=FT.dbo.Std_Cost_Compare.HBG_item_no+FT.dbo.Std_Cost_Compare.HBG_loc
where HBG.dbo.IMINVLOC_SQL.item_no in (select hbg_item_no
from ft.dbo.Std_Cost_Compare)
January 20, 2009 at 11:03 pm
Try running this query.
SELECT I.item_no,I.std_cost,I.loc, C.HBG_std_cost
FROM HBG.dbo.IMINVLOC_SQL I
LEFT JOIN FT.dbo.Std_Cost_Compare C ON I.item_no = C.item_no AND I.loc = C.HBG_loc
A bit easier to read than yours. You're getting fewer rows because of your inner join. I'm assuming you're adding the two fields together in each table because you weren't aware you could use an AND in a join definition. Using an AND instead eliminates the need for your IN and several other possible issues that arise when doing things the other way. I used a left join to illustrate rows that are in your IMINVLOC table that may not have matching values in Std_Cost_Compare. SQL 2008 depracates 4 part (possibly even 3 part, can't remember) naming in the SELECT clause, so whether you like aliases or not, you're gonna have to start using them eventually.
January 20, 2009 at 11:10 pm
Garadin (1/20/2009)
Try running this query.
SELECT I.item_no,I.std_cost,I.loc, C.HBG_std_costFROM HBG.dbo.IMINVLOC_SQL I
LEFT JOIN FT.dbo.Std_Cost_Compare C ON I.item_no = C.item_no AND I.loc = C.HBG_loc
A bit easier to read than yours. You're getting fewer rows because of your inner join. I'm assuming you're adding the two fields together in each table because you weren't aware you could use an AND in a join definition. Using an AND instead eliminates the need for your IN and several other possible issues that arise when doing things the other way. I used a left join to illustrate rows that are in your IMINVLOC table that may not have matching values in Std_Cost_Compare. SQL 2008 depracates 4 part (possibly even 3 part, can't remember) naming in the SELECT clause, so whether you like aliases or not, you're gonna have to start using them eventually.
Yes, 3-part naming convention in the SELECT is depreciated. We need to get used to 2-part naming conventions in the SELECT. A good reason to use aliases on the table names in your FROM clauses.
January 21, 2009 at 11:38 am
Cool!! Thanks guys, that makes a lot of sense and fixed my problem. Great Community
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply