Missing data from converting OR condtion in Join to Another JOIN

Question

Few days back I asked how to change OR condtion (left join) into another left join. Here is the code below :

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:

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

When you add another table to the query it doesn't automagically generate new rows. It sounds like you have two datasets you want in a single table. UNION ALL is how you do that. Quite strange company standards that you can't use UNION ALL. About the only other thing I can think of would be to create a temp table and populate it with two insert statements. — Sean Lange, Mar 11 '15 at 18:46
@BogdanSahlean - is there any other way you can suggest.. except Union All — Ritesh, Mar 11 '15 at 21:44
Or condition is taking more than 45 minutes.. Just wnated to optimized that and mke it faster. — Ritesh, Mar 11 '15 at 21:51
@Ritesh Union All and Joins are different concepts. See an explanation here http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union. Also, correct me if I'm wrong, but the first query above (which uses OR) tries to access data from 2 tables but second query includes third table. Once you understand that, try to run your query in pieces and see if each part is giving you correct result. You can also use CTE for that. I can't provide any solution unless I completely understand your question. — sanmis, Mar 12 '15 at 02:13
@sanmis ...I have little bit idea how Unino all and Joins work.. I am just splitting OR condition into another join with the same table but diferrent alias. With different part join they are working fine. But together they are not summing up data.. records are only from first join coming even though few records falls in 2ns join condition. — Ritesh, Mar 12 '15 at 15:16

score 1 · Answer 1 · answered Mar 12 '15 at 12:00

1

First of all please notice that you're using t2 instead of t3 in the second left join :

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 t3.ID IS NULL
    )

Anyhow, without understanding what you'r trying to do and how do you want to see your result, it's hard to answer you.

answered Mar 12 '15 at 12:00

Pavel

1,627
15
12

T2 and T3 are same table.. I have to get data from same table with two different sets of condition..(Check my question with OR condition).. Any record which falls into both the joins condition doesnt show 2nd join records. – Ritesh Mar 12 '15 at 15:10

sanmis · Answer 2 · 2015-03-14T00:42:56.553

0

You're using left join which just gives you everything from left result set. Since, you want data from t3 which is in right, change second left join to be either right join or full join, depending on your requirements. One of the following query can give you correct result:

Editing 14-03-2015

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

edited Mar 14 '15 at 00:42

answered Mar 12 '15 at 21:06

sanmis

515
1
7
22

I tried it above queries, they are not giving me desired data. Thanks for he help anyways – Ritesh Mar 13 '15 at 22:18
@Ritesh, I've made an edit to my answer. This time using full outer join for both. If you don't get all data using this, then there is some problem with your data itself. Full outer join gives you all data from both tables. If you're getting lots of nulls that means the tables are completely exclusive. – sanmis Mar 14 '15 at 00:45

Missing data from converting OR condtion in Join to Another JOIN

2 Answers2