CODEWITHSUNDEEP
sql-servert-sql
SKGeek
SKGeek

Reputation: 81

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

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?

Upvotes: 2

Views: 2924

Answers (3)

StuartLC
StuartLC

Reputation: 107237

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.)

Upvotes: 1

Dr. Wily's Apprentice
Dr. Wily's Apprentice

Reputation: 10280

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.

Upvotes: 1

Mike Cheel
Mike Cheel

Reputation: 13106

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

Upvotes: 2

Related Questions