Sql server 2005/2008 conditional connection

Question

Is there such a thing as conditional join:

SELECT *
FROM TABLE1 A
    IF (a=='TABLE2') THEN INNER JOIN TABLE2 B ON A.item_id=B.id
    ELSE IF (a=='TABLE3') THEN INNER JOIN TABLE3 C ON A.item_id=C.id

So far this field is in table 1.

I like to use this in stored procedures without using dynamic sql (without writing the query as a string and EXEC (@query)).

EDIT: I can not write:

IF (a=='TABLE2) THEN queryA
ELSE IF (a=='TABLE3') THEN queryB

Since a is a field of TABLE1.

Answer 1

EDIT . The modified answer is based on the comment below:

You can try to get smarter with some left connections. This will return more columns, so you probably want to be more picky than simple SELECT *.

SELECT *
    FROM TABLE1 A
        LEFT JOIN TABLE2 B
            ON A.item_id = B.id
                AND A.a = 'TABLE2'
        LEFT JOIN TABLE3 C
            ON A.item_id = C.id
                AND A.a = 'TABLE3'
    WHERE (B.id IS NOT NULL AND A.a = 'TABLE2')
       OR (C.id IS NOT NULL AND A.a = 'TABLE3')

Answer 2

Updated request as requested:

SELECT * FROM
(
    SELECT * 
        FROM TABLE1 A  INNER JOIN TABLE2 B 
            ON A.a='TABLE2' --This will eleminate the table rows if the value of A.a is not 'TABLE2' 
         AND A.item_id=B.id) A,
             (SELECT * FROM
             INNER JOIN TABLE3 C 
            ON A.a='TABLE3' --This will eleminate the table rows if the value of A.a is not 'TABLE3'
            AND A.item_id=C.id 
                ) B
) a