Help with select--from - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with select--from

Author

Topic

wanni
Starting Member

14 Posts

Posted - 2006-06-11 : 21:48:42

there are two database , one is called ef2kWeb and the other one is db2,
db2 has following tables : Budget, Department
ef2kWeb has following talbes: bel008_a, resda

now i wanted to join these two database ,

following query is what I have so far:

SELECT DISTINCT
Budget.Budget_ID, SUM(CONVERT(int,
REPLACE(LEFT(ef2kWeb.dbo.bel008_a.bel008_a019,
LEN(ef2kWeb.dbo.bel008_a.bel008_a019) - CHARINDEX('.',
REVERSE(ef2kWeb.dbo.bel008_a.bel008_a019))), ',', ''))) AS TotalCost,
ef2kWeb.dbo.bel008_a.bel008_a007, Budget.Budget, Budget.Remark,
Department.Department
FROM Budget INNER JOIN
ef2kWeb.dbo.bel008_a ON
Budget.Budget_ID = ef2kWeb.dbo.bel008_a.bel008_a010 iNNER JOIN
Department ON
ef2kWeb.dbo.bel008_a.bel008_a007 = Department.Department_ID and Budget.Department_ID=Department.Department_ID

Now, i wanted to add one more condition,
which is ,
bel008_a inner join resda on bel008_a.bel008_a001=resda.resda001 and bel008_a.bel008_a002=resda.resda002

how should i add it,
I have tried
FROM
Budget INNER JOIN
ef2kWeb.dbo.bel008_a ON
Budget.Budget_ID = ef2kWeb.dbo.bel008_a.bel008_a010 INNER JOIN
Department ON ef2kWeb.dbo.bel008_a.bel008_a007 = Department.Department_ID and Budget.Department_ID=Department.Department_ID INEER JOIN ef2kWeb.dbo.resda on bel008_a.bel008_a001=resda.resda001 and bel008_a.bel008_a002=resda.resda002

but it doesn't work , I guess its becuase both resda and bel008_a are not in current db ( db2) they are in ef2kweb, and they have no relation with db2.dbo.Budget

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-12 : 02:17:23

1. be consistent (if using db prefix, use the convention in all tables).
2. use table alias
3. Use indentation for readability
FROM
db2.dbo.Budget B INNER JOIN ef2kWeb.dbo.bel008_a E ON (
B.Budget_ID = E.bel008_a010
)
INNER JOIN db2.dbo.Department D ON (
E.bel008_a007 = D.Department_ID
and B.Department_ID = D.Department_ID
)
INNER JOIN ef2kWeb.dbo.resda R ON (
E.bel008_a001=R.resda001
and E.bel008_a002 = R.resda002
)

see if this works. If not, post the actual error message.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources