Join two tables only if the first contains certain values if not join another

Question

I am trying to write a query that join to a TableA another TableB if TableA.Column1 contains numeric values and Join to TableA another TableC if TableA.Column1 contains varchar values.

Is there a way to write such a query?

Answer 1

How about something like this? You will need to cast the columns appropriate to some middle ground.

SELECT * 
FROM TableA a 
INNER JOIN TableB b ON b.Columns1 = a.Column1
    AND ISNUMERIC(a.Column1) = 1
WHERE 1=1
UNION
SELECT * 
FROM TableA a 
INNER JOIN TableC c ON c.Columns1 = a.Column1
    AND ISNUMERIC(a.Column1) = 0

Answer 2

The table design sounds questionable, but I think this query is a simple way to achieve what you're asking for.

SELECT
    TableA.Column1,
    TableB.Column2,
    TableC.Column2,
    ISNULL(TableB.Column2, TableC.Column2)
FROM TableA
LEFT OUTER JOIN TableB ON
    ISNUMERIC(TableA.Column1) = 1
    AND TableA.Column1 = TableB.Column1
LEFT OUTER JOIN TableC ON
    ISNUMERIC(TableA.Column1) = 0
    AND TableA.Column1 = TableC.column1

As Mike Cheel points out, you may need to do some casting.

Also, with this approach you will need to consider the possibility that there is a record in TableA that does not match anything in TableB or TableC, because this is using outer joins. If you don't want those records in your result, you can just exclude them with a condition in your WHERE clause.

Answer 3

Along the lines of JNK's comment, here's a way where you could go about it which at least tries to encapsulate the design issue a bit, by add 2 Computed columns to your table, which represent placeholders for the INT and VARCHAR foreign keys.

ALTER TABLE MyTable ADD IntJoinColumn AS 
CASE WHEN ISNUMERIC(BadJoinColumn) = 1 
     THEN CAST(BadJoinColumn AS INT) 
     ELSE NULL 
END;
ALTER TABLE MyTable ADD VarCharJoinColumn AS 
     CASE WHEN ISNUMERIC(BadJoinColumn) = 1 
          THEN NULL 
          ELSE BadJoinColumn 
     END;

You can then join in a more 'readable' manner, like so:

SELECT mt.* 
    FROM MyTable mt
    INNER JOIN MyIntJoinTable ON IntJoinColumn = MyIntJoinTable.Id 
UNION ALL
SELECT mt.* 
    FROM MyTable mt
INNER JOIN MyVarCharJoinTable ON VarCharJoinColumn = MyVarCharJoinTable.VarCharId;

SQLFiddle Here

(The NULL mapping has the effect of filtering out the 'incorrect' data types by eliminating them during the INNER JOIN.)