6 Replies Latest reply: Jul 1, 2014 3:45 AM by Massimo Grossi

Problem with outer join

Hi,

excuse for my english,

I have a doubt about the use of OUTER JOIN:

My script is this:

Table_OuterJoin1:

PK, Product, invent_sum

1, 'P1', 5

2, 'P2', 10

3, 'P3', 15

4, 'P4', 20

1, 'P1', 34

];

Tabla_OuterJoin2:

PK, Product, ordered

1, 'P1', 7

4, 'P4', 3

5, 'P5', 5

];

OUTER JOIN(Tabla_OuterJoin1)

The result of this script isn't as I expected:

PK                       product                                      ordered                  invent_sum

 1 P1 7 2 P2 10 3 P3 15 4 P4 3 20 5 P5 5

I expected two rows for the PK=1

1                       P1                                    7                                  5

1                       P1                                    7                                  34

How do I gain this result?

• Re: Problem with outer join

Outer Join returns all the combinations from loaded records, you need an INNER JOIN.

Finally remember to drop Table_OuterJoin1 in thid way:

DROP TABLE Table_OuterJoin1;

Let me know

• Re: Problem with outer join

The result you expect is the result I get when I join your two tables.

• Re: Problem with outer join

Hi Javier,

Some cases you need outer join to get all possible values, so try with:

Table_OuterJoin1:

PK, Product, invent_sum

1, 'P1', 5

2, 'P2', 10

3, 'P3', 15

4, 'P4', 20

1, 'P1', 34

];

OUTER JOIN

PK, Product, ordered

1, 'P1', 7

4, 'P4', 3

5, 'P5', 5

];

-JFlorian

• Re: Problem with outer join

```Table_OuterJoin1:
PK, Product, invent_sum
1, 'P1', 5
2, 'P2', 10
3, 'P3', 15
4, 'P4', 20
1, 'P1', 34
];

outer join (Table_OuterJoin1)          // you can remove outer
//  Tabla_OuterJoin2: