14 Replies Latest reply: Oct 12, 2017 9:05 AM by youssef belloum RSS

    keep joins with matching values on two differents keys

    youssef belloum

      Hi All,

       

      I have three tables: INVOICE, MAT, CUST with values like this:

       

      INVOICE:

      ID,     ID_CUST,     ID_MAT

      1,     20,                  30

      2,     21,                  31

      3,     22,                  32

      4,     23,                  33

      5,     24,                  34

      6,     25,                  35

      7,     26,                  36

      8,     27,                  37

      9,     28,                  38

      10,   29,                  39

       

      MAT:

      ID_MAT

      33

      34

      35

      36

      37

      38

      39

      40

       

      CUST:

      ID_CUST

      20

      21

      22

      23

      24

      25

      26


      when in load these table with INNER KEEPs on the Invoice i should have this:


      INVOICE:

      ID,     ID_CUST,     ID_MAT

      1,     20,                  30

      2,     21,                  31

      3,     22,                  32

      4,     23,                  33

      5,     24,                  34

      6,     25,                  35

      7,     26,                  36

      8,     27,                  37

      9,     28,                  38

      10,   29,                  39

       

       

      BUT, In my final Fact table (Invoice) I want to have ONLY  the lines matching with these 3 tables, like this:

       

      INVOICE:

      ID,     ID_CUST,     ID_MAT

      1,     20,                  30  //not this line

      2,     21,                  31  //not this line

      3,     22,                  32  //not this line

      4,     23,                  33  //THIS LINE IS OK

      5,     24,                  34  //THIS LINE IS OK

      6,     25,                  35  //THIS LINE IS OK

      7,     26,                  36  //THIS LINE IS OK

      8,     27,                  37  //not this line

      9,     28,                  38  //not this line

      10,   29,                  39  //not this line

       

       

      I tried with INNER KEEPs, with WHERE EXISTS.. but i could not get the result i want.

       

      Please take a look

       

      Thanks,

      YB