I have to create a query that verifies if a column is not null and join using it, but if it's null I must to join another column.
if was possible, must be something like that
SELECT A FROM DBTEST
IF ( NOT B = NULL)
INNER JOIN DBTEST2.B = DBTEST.B
ELSE
INNER JOIN DBTEST2.C = DBTEST.B
Any idea?
This should work
SELECT A
FROM DBTEST
Inner join DBTEST2
on DBTEST.B = case when DBTEST2.B is null then DBTEST2.C else DBTEST2.B end
SELECT a
FROM dbtest
JOIN dbtest2
ON dbtest.b = dbtest2.b
UNION ALL
SELECT a
FROM dbtest
JOIN dbtest2
ON dbtest.b = dbtest2.c
WHERE dbtest2.b IS NULL
This will probably perform better than using a case expression inside the join condition, as the optimiser will be able to choose appropriate indexes if they exist, or at least to choose a join method based on the table structures.
SELECT a
FROM dbtest
INNER JOIN (SELECT Isnull(dbtest2.b, dbtest2.c) Col
FROM dbtest2)TT
ON TT.col = dbtest.b
I think this may solve your problem
