CODEWITHSUNDEEP
sqljoinleft-joinoracle12c
Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

sql joins returns multiple row than expected

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.

so if the join doesnt work is there any other method to get this desired output.

Upvotes: 0

Views: 55

Answers (1)

NickW
NickW

Reputation: 9818

  1. Your SQL statement doesn't match your tables: there is no "language" column in zz_value
  2. When I run your SQL (without this language column) it returns 4 rows - so there doesn't seem to be any issue

Please can you review your data/SQL and make sure they are accurate as there would seem to be a mismatch between what you have written and what you are running, if you are seeing 16 rows being returned

UPDATE 1

Based on the data you have supplied and the JOIN condition in your SQL, each record in zz_value is going to match multiple records in the zz_gg table - which is why you are getting more than 24 records in your resultset.

For example, you have 8 records in zz_gg with these values:

GG_ID   POS_ID  NACH_ID COUNT(1)
9863    176959  2416    8

So each record in zz_value that has these values will return 8 records in your resultset

Upvotes: 1

Related Questions