Join tables on two different database dynamically

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

General SQL Server Forums

New to SQL Server Programming

Join tables on two different database dynamically

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-07-11 : 06:43:34

----------------
SELECT <coldsn>,<col2>
FROM
<MyTable> X
INNER JOIN
( CASE <coldsn>
WHEN 'dsn1' THEN 'db1.dbo.newtable'
WHEN 'dsn2' THEN 'db2.dbo.newtable'
END

)
T ON T.<col2> = X.<col2>
WHERE
condition

----------------------

There are 3 tables,' Mytable' in the current db and table with same name 'newtable' on two others dbs.(db1,db2).
I would like to join the 'Mytable' with either of two tables dynmically.
Can it be managed in a single query? The above sql statement shows error near the case.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-11 : 06:55:01

Assuming the two tables have same columns, you could do this:

SELECT <coldsn>,<col2>
FROM
<MyTable> X
INNER JOIN
(
SELECT * FROM dbo1.dbo.Newtable WHERE @dsn1 = 1
UNION ALL
SELECT * FROM dbo2.dbo.Newtable WHERE @dsn2 = 1
)
T ON T.<col2> = X.<col2> 
WHERE
condition

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-11 : 06:55:12

You need dynamic sql for that - if you really need to do that...

http://www.sommarskog.se/dynamic_sql.html

No, you're never too old to Yak'n'Roll if you're too young to die.

No you don't need dynamic sql for that

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-11 : 06:56:38

can you give an example?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-11 : 06:57:02

See sunita's post

No, you're never too old to Yak'n'Roll if you're too young to die.

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-07-11 : 07:26:48

The join is with one of the tables at a time .Let me give some more detail
---------------
Country coldsn dbname
Aust gau db1
Japan gp db2
---------------------------------

if the mytable.coldsn = gau,

mytable inner join 'db1.dbo.newtable'

if the mytable.coldsn = gp,
mytable inner join 'db2.dbo.newtable'

ie. the join has to happen based on the value mytable.coldsn

quote:
Originally posted by sunitabeck

Assuming the two tables have same columns, you could do this:
SELECT <coldsn>,<col2>
FROM
<MyTable> X
INNER JOIN
(
SELECT * FROM dbo1.dbo.Newtable WHERE @dsn1 = 1
UNION ALL
SELECT * FROM dbo2.dbo.Newtable WHERE @dsn2 = 1
)
T ON T.<col2> = X.<col2>
WHERE
condition

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-11 : 07:48:03

quote:
The join is with one of the tables at a time .Let me give some more detail
---------------
Country coldsn dbname
Aust gau db1
Japan gp db2
---------------------------------

if the mytable.coldsn = gau,

mytable inner join 'db1.dbo.newtable'

if the mytable.coldsn = gp,
mytable inner join 'db2.dbo.newtable'

ie. the join has to happen based on the value mytable.coldsn

In your example, you have two rows, one with coldsn = gau and the other with coldsn = gp. Doesn't that mean that you will need to join to BOTH tables? If you MUST pick one table at a time, how do you decide which one to pick in this example?

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-07-11 : 08:13:19

Correct. The first records needs to be joine with db1 and the second one with db2.
The field to identify this is 'coldsn'. I need to retreive these records based on condition that
it has an entry on 'dbo.newtable' which is present on both dbs. Is it possible to join the records in the same table
dynamically to different dbs in the same sql statement?

quote:
Originally posted by sunitabeck

quote:
The join is with one of the tables at a time .Let me give some more detail
---------------
Country coldsn dbname
Aust gau db1
Japan gp db2
---------------------------------

if the mytable.coldsn = gau,

mytable inner join 'db1.dbo.newtable'

if the mytable.coldsn = gp,
mytable inner join 'db2.dbo.newtable'

ie. the join has to happen based on the value mytable.coldsn
In your example, you have two rows, one with coldsn = gau and the other with coldsn = gp. Doesn't that mean that you will need to join to BOTH tables? If you MUST pick one table at a time, how do you decide which one to pick in this example?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-11 : 08:49:54

That sounds like you will need to join both tables - not one table or the other. May be this?

SELECT
	m.col1 AS MCol1,
	m.col2 AS MCol2,
	m.Country,
	m.coldsn,
	m.dbName,
	COALESCE(t1.col1,t2.col1) AS Tcol1,
	COALESCE(t1.col2,t2.col2) AS Tcol2
FROM
	myTable m
	LEFT JOIN db1.dbo.newtable t1 ON t1.col2 = m.col2 AND m.coldsn = 'gau'
	LEFT JOIN db2.dbo.newtable t2 ON t2.col2 = m.col2 AND m.coldsn = 'gp'

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-11 : 13:47:35

SELECT <coldsn>,<col2>
FROM
<MyTable> X
INNER JOIN
(
SELECT *, 'gau' as NEW_COLUMN FROM dbo1.dbo.Newtable WHERE @dsn1 = 1
UNION ALL
SELECT *, 'gp' as NEW_COLUMN FROM dbo2.dbo.Newtable WHERE @dsn2 = 1
)
T
ON X.<col2> = T.<col2>
AND T.NEW_COLUNS = X.coldsn
WHERE
condition

razeena
Yak Posting Veteran

54 Posts

Posted - 2012-07-12 : 02:11:31

Thank you jleitao and sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources