0

I'm trying to join depending on whether table1.column1 is null or not null.

For, example I have two tables, table1 and table2, and the query:

SELECT A.column2 FROM table1 A , table2 B WHERE if A.column1 is not null then (A.column1=B.column1) else if A.column1 is null then (A.column3 = B.column1);
Improve this question
5
  • 2
    It's advisable to use the normal JOIN syntax instead of putting all the tables on the FROM clause, which is deprecated. Commented Dec 19, 2014 at 23:27
  • This does not make sense. A.column1 is null then why would you want to join with B.column1. Commented Dec 19, 2014 at 23:28
  • There's a severe logic problem here. If A.column1 is NULL it is not possible for A.column1 = B.column1. Commented Dec 19, 2014 at 23:28
  • @Aaron Bertrand Sorry for not framing it properly... Commented Dec 19, 2014 at 23:34
  • What I'm trying to check if A.column1 is NOT null then (A.column1=B.column1) else if A.column1 is null then (A.column3=B.column1) Commented Dec 19, 2014 at 23:47

2 Answers 2

Reset to default
1

Try this:

SELECT A.column2 FROM table1 A JOIN table2 B ON B.column1 = A.column1 OR (A.column1 IS NULL AND B.column1 = A.column3)

Note that B.column1 = A.column1 will never be true if either B.column1 or A.column1 is NULL.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Try this...

SELECT A.column2 FROM table1 A JOIN table2 B ON NVL(A.column1, A.column3) = b.Column1

If b.Column1 can also be null, and you want to compare two null columns, you can try this...

SELECT A.column2 FROM table1 A JOIN table2 B ON NVL(b.Column1, 'X') = COALESCE(A.column1, A.column3, 'X')

Assuming b.Column1 can never have the value 'X'

Improve this answer

7 Comments

it is giving me this error ORA-00904: "ISNULL": invalid identifier 00904. 00000 - "%s: invalid identifier"
Your tag says sql-server ? Change ISNULL to NVL
I tried using NVL.. But its not giving me the right result
Then you should clarify your question. This query does exactly what you're asking for.
Can you post sample data, with the expected result
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.