Missing data from converting OR condtion in Join to Another JOIN

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I changeg OR condtion (left join) into another left join. Here is the code below :

SQL

SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.ID = t2.ID
    OR (
        t1.col1 = t2.col1
        AND t1.col2 = t2.col2
        AND t1.col3 = t2.col3
        AND t2.ID IS NULL
    )

I converted to OR condition like below:

SQL

SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.ID = t2.ID
LEFT JOIN Table2 t3
    ON (
        t1.col1 = t3.col1
        AND t1.col2 = t3.col2
        AND t1.col3 = t3.col3
        AND t2.ID IS NULL
    )

But i am not getting records for second left join in seperate row.

With OR condition result are like :

T1.ID  T2.ID
1      a
2      b
3      c
4      d

But with second left join its lile below:

T1.ID  T2.ID
1      a
2      b
3      c

4rth record is from second left join. How can i bring that in left join. I dont have option for using UNION ALl as per company standards.

Please help.
Thanks

Posted 11-Mar-15 11:50am

Member 11517519

Updated 11-Mar-15 21:50pm

Tomas Takac

v2

Add a Solution

Comments

[no name] 11-Mar-15 18:11pm

Guessing: Try changing "AND t2.ID IS NULL" to "AND t3.ID IS NULL"

Member 11517519 11-Mar-15 18:34pm

I tried that also but giving same result

[no name] 11-Mar-15 21:38pm

Tried it, works for me (regardless of my first guess). No idea where the problem could be, sorry. Btw, the second column (a, b, c) should have "col1" as header or something else than "T2.ID" in any case, right? Otherwise your join wouldn't work at all ;-)

Tomas Takac 12-Mar-15 4:25am

You are not showing the complete inputs and outputs. The second column is probably T2.col1 and not T2.ID as manchanx pointed out. I have the strong feeling that you didn't include the last line in your second output which should be 4,NULL because even though the right side was not matched T1 still has 4 rows, right?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Answer 1 · 2015-03-11T22:37:00

I ran the queries on simplified tables and here are the results:

id          col  id          col
----------- ---- ----------- ----
1           A    1           A
2           B    2           B
3           C    3           C
4           D    NULL        D

id          col  id          col  id          col
----------- ---- ----------- ---- ----------- ----
1           A    1           A    NULL        NULL
2           B    2           B    NULL        NULL
3           C    3           C    NULL        NULL
4           D    NULL        NULL NULL        D

As you can see you still get the 4th T2.col value in the first output thanks to the OR. In the second output T2.col is null because it's not matched by ID. What you want is T3.col value. Your query should look like this:

SQL

SELECT t1.id, coalesce(t2.col, t3.col) as col
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.ID = t2.ID
LEFT JOIN Table2 t3
    ON (
        t1.col = t3.col
        AND t2.ID IS NULL
    )

Then the result is as expected:

id          col
----------- ----
1           A
2           B
3           C
4           D