0

I wanted to join table zz_value with zz_gg.

table zz_value looks like:

nach_id  ins_id  pos_id  value data_id gg_id
2416    647415  176959  266355  166991 9863
2416    647421  176959  266355  166991 9863
2416    647422  176959  266355  166991 9863
2416    647425  176959  266355  166991 9863
2416    647427  176959  266355  166991 9863
2416    647429  176959  266355  166991 9863
2416    647430  176959  266355  166991 9863
2416    647431  176959  266355  166991 9863
2416    647412  176959  266355  175068 9867
2416    647423  176959  266355  175068 9867
2416    647428  176959  266355  175068 9867
2416    647433  176959  266355  175068 9867
2416    647434  176959  266355  175068 9867
2416    647411  176959  266355  175069 9868
2416    647414  176959  266355  175069 9868
2416    647417  176959  266355  175069 9868
2416    647418  176959  266355  175069 9868
2416    647419  176959  266355  175069 9868
2416    647420  176959  266355  175069 9868
2416    647413  176959  266355  175070 9869
2416    647416  176959  266355  175070 9869
2416    647424  176959  266355  175070 9869
2416    647426  176959  266355  175070 9869
2416    647432  176959  266355  175070 9869

table zz_gg looks like:

nach_id  ins_id  pos_id data_id gg_id
2416    647410  176959  266354  9868
2416    647389  176959  266352  9863
2416    647388  176959  266352  9869
2416    647392  176959  266352  9867
2416    647394  176959  266352  9868
2416    647391  176959  266352  9863
2416    647393  176959  266352  9869
2416    647387  176959  266352  9868
2416    647390  176959  266352  9867
2416    647397  176959  266353  9863
2416    647396  176959  266353  9867
2416    647395  176959  266353  9869
2416    647402  176959  266353  9869
2416    647401  176959  266353  9868
2416    647398  176959  266353  9863
2416    647399  176959  266353  9863
2416    647400  176959  266353  9868
2416    647406  176959  266354  9868
2416    647409  176959  266354  9867
2416    647405  176959  266354  9863
2416    647404  176959  266354  9867
2416    647403  176959  266354  9863
2416    647408  176959  266354  9869
2416    647407  176959  266354  9863

I have used the below statement.

 select t.nach_id,t.pos_id,t.gg_id,g.data_id,t.value
  from zz_value t
  left  join zz_gg g          
         on t.gg_id = g.gg_id
         and g.pos_id = t.pos_id
         and g.nach_id = t.nach_id
         where t.pos_id = 176959
         and t.nach_id = 2416;

expected output:

nach_id pos_id   gg_id  data_id  value
2416    176959   9863   266353  166991
2416    176959   9863   266354  166991
2416    176959   9863   266354  166991
2416    176959   9863   266354  166991
2416    176959   9863   266353  166991
2416    176959   9863   266353  166991
2416    176959   9863   266352  166991
2416    176959   9863   266352  166991
2416    176959   9867   266353  175068
2416    176959   9867   266352  175068
2416    176959   9867   266352  175068
2416    176959   9867   266354  175068
2416    176959   9867   266354  175068
2416    176959   9868   266353  175069
2416    176959   9868   266354  175069
2416    176959   9868   266353  175069
2416    176959   9868   266352  175069
2416    176959   9868   266352  175069
2416    176959   9868   266354  175069
2416    176959   9869   266353  175070
2416    176959   9869   266352  175070
2416    176959   9869   266352  175070
2416    176959   9869   266354  175070
2416    176959   9869   266353  175070

When i join this table I expect 24 rows but i am getting 100+ rows as result. I have also tried with simple join instead of left join. It shows the same result as well.

2

In this case Oracle works as it should, its your expectation that is incorrect.

Why do you expect 24 rows?

zz_value table has 8 rows with 9863 gg_id. zz_gg table has 8 rows with 9863 gg_id. pos_id and nach_id are the same in all rows in your sample. With your join conditions, this alone return 64 rows for gg_id 9863.

Your select with those columns returns duplicated rows. You can reduce that amount by using select distinct ..., but even that will return more than 24 rows because of the data_id column in zz_gg.

4
  • ya but i have joined using t.gg_id = g.gg_id.. so i expect this to return only 24.
    – newintodev
    Nov 18 '20 at 9:45
  • @newintodev And that is where you are wrong. Nov 18 '20 at 9:52
  • so if the Joins doesnt work is there any other method by which i can get the expected output as mentioned ?
    – newintodev
    Nov 18 '20 at 11:18
  • You have 8 rows in each table with same gg_id ( 9863 ). So you have to add another condition (besides the t.gg_id = g.gg_id and g.pos_id = t.pos_id and g.nach_id = t.nach_id) to define which row should be connected to which. Otherwise, you'll get all the 64 combinations Nov 18 '20 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.