Friday, February 10, 2012

Can I Join Table Variables? It doesnt seem to compile

I want to join two (or more) table variables

DECLARE @.A TABLE (x int...)
DECLARE @.B TABLE (y int...)

INSERT INTO @.A

INSERT INTO @.B

--this code doesnt work
SELECT * FROM
@.A LEFT JOIN @.B
ON @.A.x = @.B.y

Are these things even possible with variable tables?

thanks.USE Northwind
GO

DECLARE @.A TABLE (x int IDENTITY(1,1), z char(1) )
DECLARE @.B TABLE (y int IDENTITY(1,1), z char(1))

INSERT INTO @.A(z)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'

INSERT INTO @.B(z)
SELECT 'W' UNION ALL
SELECT 'X' UNION ALL
SELECT 'Y' UNION ALL
SELECT 'Z'

SELECT *
FROM @.A a LEFT JOIN @.B b
ON a.x = b.y

...pronoun trouble...B.B.|||You must use aliases when referencing table variables. Hence Brett's use of:

SELECT *
FROM @.A a LEFT JOIN @.B b
ON a.x = b.y

where this would fail:

SELECT *
FROM @.A LEFT JOIN @.B
ON @.A.x = @.B.y

blindman

No comments:

Post a Comment