Forum Discussion

aktripathi2506's avatar
aktripathi2506
Helper IV
9 years ago

Problem with Left outer join when to create the relationship between tables

Hi,

 

I have two tables (Table A and Table B), they both are having millions of data.

In both the table I have a column name Serial number.

 

And when I create relation between these two tables based on the column Serial number I am able to do so very easily without any transformation.

but when I merge one table (Table A) with the third one (Table C) by left outer join then I am not able to create the relation, why?

 

With left outer join still all the item numbers should be same in Table A, only few more columns should be added with either the relevant values or by null.

 

Please correct me if I am wrong.

Also please let me know a possible way to perform this action. Because I need to connect all these three tables as I need their column information.

 

 

Data set example

Table A 

 

Item NumberSerial NumberdateName
IN 12111 AJK
IN 21222 RIM
IN 25123 TIM
IN 32124 JAK
IN 40137 PAUL
IN 45146 KEN

  

Table B

 

Serial NumberdateNameHOURS
111 AJK3
222 RIM5
123 TIM7
124 JAK2
137 PAUL70
146 KEN10

 

I am successfully able to create relation between thee two table based on column Serial Number.

 

Now I have table C from which I want to use the column Deadline date and number of days for the deadline.

 

Table C

 

Item NumberDeadlineDeadline dateName
IN 122 days AJK
IN 915 days RIM
IN 259 days TIM
IN 326 days JAK
IN 701 days 

KEN

 

For this I create a left join to get the deadline of all the items which are present in table A. But when I do merge query with left outer join I am not able to create the relation between Table A and Table B.

 

Please advise,

Thanks.

Need Help

5 Replies

    • jahida's avatar
      jahida
      Impactful Individual
      9 years ago
      Initial thought is that A used to have unique values in the column you're doing the relationship on, but no longer has unique values after the outer join.
      • aktripathi2506's avatar
        aktripathi2506
        Helper IV
        9 years ago

        thanks jahida,

         

        Yes to create the relation one of the table sould have unique value in that column.

         

        What I did is I exported the data into excel and check for any duplicate but I did not get any.

         

        And according to the defination of left join also it should have all the row from table A (first table). From 2nd table we only get additional column not row..right?

         

        If it was able to make relationship before the join than why not after join.