Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Question Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •