December 2, 2010 at 11:34 am
I'm running a report that uses like table in multiple databases. The current set up uses something like this:
SELECT Col1, Col2
FROM Thundercats.dbo.Characters
UNION
SELECT Col1, Col2
FROM Voltron.dbo.Characters
UNION
SELECT Col1, Col2
FROM HeMan.dbo.Characters
This works but the query is huge so I'd like to do something like this instead:
SELECT Col1, Col2
FROM Thundercats.dbo.Characters
JOIN Voltron.dbo.Characters
ON Thundercats.dbo.Characters.Name = Voltron.dbo.Characters.Name
JOIN HeMan.dbo.Characters
ON Voltron.dbo.Characters.Name = HeMan.dbo.Characters.Name
This returns the error below:
The objects "Thundercats.dbo.Characters" and "Voltron.dbo.Characters" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
December 2, 2010 at 11:54 am
You need to do something like this:
SELECT T.Col1, T.Col2, V.Col1, V.Col2, H.Col1, H.Col2
FROM Thundercats.dbo.Characters AS T
JOIN Voltron.dbo.Characters AS V
ON T.Name = V.Name
JOIN HeMan.dbo.Characters AS H
ON V.Name = H.Name
That will remove the error. The 'exposed name' bit is they all have the same table name.
However, that's not going to give you a list, it's going to give you a recordset where the same name is in all three tables. (And if memory serves, Tigra wasn't in Voltron, and Ram-Man seriously wasn't in either...) 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2010 at 12:53 pm
I agree with Craig, you need to alias the tables in the from clause, and you really don't want a JOIN for the reasons in the contextual example that Craig gave. I'm not sure what "huge" means for your query, but "UNION" will force a distinct, where you may benefit from "UNION ALL". If you truly need to join your output from the tables in the separate databases, you may want to look at the EXCEPT and INTERSECT operands.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply