Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
9 years ago

how to join two tables.

Hi All, i want to know how can i join two tables with a common feild to make a resultant table.
Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
9 years ago

BhaveshPatel Actually i have created realtionship between the  tables but i have to summerize the amount feild based on id which is in different table that why i need a join to create a new resultant table.

but i will try the merge query.

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

Love to recreate a solution for you if you can post sample data and expected output.

  • Satish_Kumar's avatar
    Satish_Kumar
    Icon for Helper V rankHelper V
    9 years ago

    BhaveshPatel

     

    i am sending you the demo data for this issue.

     

     

    Document No_Buy-from Vendor No_Posting GroupAmount Including Tax
    PINV/0809/00001VEN0027FINISHED220895
    PINV/0809/00002VEN0027FINISHED625408
    PINV/0809/00003VEN0027FINISHED33098
    PINV/0809/00004VEN0004FINISHED322341
    PINV/0809/00005VEN0004FINISHED3961
    PINV/0809/00006VEN0004 49838
    PINV/0809/00006VEN0004 0.02
    PINV/0809/00007VEN0027FINISHED15912
    PINV/0809/00008VEN0027FINISHED336177
    PINV/0809/00009VEN0027FINISHED710793
    PINV/0809/00010VEN0004FINISHED2154058
    PINV/0809/00011VEN0006FINISHED23204
    PINV/0809/00012VEN0006FINISHED946444
    PINV/0809/00013VEN0004 191290
    PINV/0809/00013VEN0004 -0.45
    PINV/0809/00014VEN0039 48687
    PINV/0809/00015VEN0040COMPUTERS30720
    PINV/0809/00016VEN0041OFFICE EQU1260
    PINV/0809/00016VEN0041OFFICE EQU1500
    PINV/0809/00017VEN0013UPS & BATT73500
    PINV/0809/00018VEN0010FINISHED22781
    PINV/0809/00018VEN0010FINISHED5923
    PINV/0809/00019VEN0004FINISHED1057044.34
    PINV/0809/00019VEN0004 -0.34
    PINV/0809/00020VEN0004FINISHED8225
    PINV/0809/00021VEN0004 147258
    PINV/0809/00021VEN0004 -0.09
    PINV/0809/00022VEN0027FINISHED301702.91
    PINV/0809/00022VEN0027 0.09
    PINV/0809/00023VEN0027FINISHED39671.1
    PINV/0809/00023VEN0027 -0.1
    PINV/0809/00024VEN0027FINISHED678730.08
    PINV/0809/00024VEN0027 -0.08
    PINV/0809/00025VEN0027FINISHED787962.96
    PINV/0809/00025VEN0027 0.04
    PINV/0809/00026VEN0027FINISHED229900.19
    PINV/0809/00026VEN0027 -0.19
    PINV/0809/00027VEN0027FINISHED131541.53
    PINV/0809/00027VEN0027 0.47
    PINV/0809/00028VEN0006FINISHED5886013.26
    PINV/0809/00028VEN0006 -0.26
    PINV/0809/00029VEN0004 20025
    PINV/0809/00029VEN0004 -0.09
    PINV/0809/00030VEN0004FINISHED143743.03
    PINV/0809/00030VEN0004 -0.03
    PINV/0809/00031VEN0004 15416
    PINV/0809/00031VEN0004 -0.42
    PINV/0809/00032VEN0004FINISHED110657.55
    PINV/0809/00032VEN0004 0.45
    PINV/0809/00033VEN0018COMPUTERS30000
    PINV/0809/00034VEN0043COMPUTERS23500
    PINV/0809/00035VEN0004FINISHED148066.35
    PINV/0809/00036VEN0044FURNITURE167562
    PINV/0809/00037VEN0004FINISHED180676.66
    PINV/0809/00037VEN0004 200
    PINV/0809/00038VEN0010FINISHED5923
    PINV/0809/00038VEN0010 22781
    PINV/0809/00039VEN0045 37079
    PINV/0809/00040VEN0004 15416
    PINV/0809/00040VEN0004 -0.42
    PINV/0809/00040VEN0004 0.42
    PINV/0809/00041VEN0004 20025
    PINV/0809/00041VEN0004 -0.09
    PINV/0809/00041VEN0004 0.09
    PINV/0809/00042VEN0004FINISHED110657.55
    PINV/0809/00042VEN0004 0.45
    PINV/0809/00042VEN0004 -0.45
    PINV/0809/00043VEN0004FINISHED143743.03

     

    • Satish_Kumar's avatar
      Satish_Kumar
      Icon for Helper V rankHelper V
      9 years ago

      2nd table.

      No_Buy-from Vendor No_Posting DatePosting Description
      PINV/0809/00001VEN00274/15/2008 0:00Order PO00001
      PINV/0809/00002VEN00274/15/2008 0:00Order PO00002
      PINV/0809/00003VEN00274/21/2008 0:00Order PO00003
      PINV/0809/00004VEN00044/22/2008 0:00Order PO00004
      PINV/0809/00005VEN00044/22/2008 0:00Order PO00005
      PINV/0809/00006VEN00044/22/2008 0:00Order PO00006
      PINV/0809/00007VEN00275/7/2008 0:00Order PO00007
      PINV/0809/00008VEN00275/7/2008 0:00Order PO00008
      PINV/0809/00009VEN00275/8/2008 0:00Order PO00009
      PINV/0809/00010VEN00045/31/2008 0:00Order PO00010
      PINV/0809/00011VEN00065/29/2008 0:00Order PO00012
      PINV/0809/00012VEN00065/29/2008 0:00Order PO00013
      PINV/0809/00013VEN00045/12/2008 0:00Order PO00011
      PINV/0809/00014VEN00394/17/2008 0:00Order PO00015
      PINV/0809/00015VEN00406/1/2008 0:00Order PO00016
      PINV/0809/00016VEN00415/14/2008 0:00Order PO00017
      PINV/0809/00017VEN00135/12/2008 0:00Order PO00018
      PINV/0809/00018VEN00106/29/2008 0:00Order PO00019
      PINV/0809/00019VEN00047/7/2008 0:00Order PO00020
      PINV/0809/00020VEN00047/7/2008 0:00Order PO00021
      PINV/0809/00021VEN00047/7/2008 0:00Order PO00022
      PINV/0809/00022VEN00276/27/2008 0:00Order PO00023
      PINV/0809/00023VEN00276/25/2008 0:00Order PO00024
      PINV/0809/00024VEN00276/27/2008 0:00Order PO00025
      PINV/0809/00025VEN00276/27/2008 0:00Order PO00026
      PINV/0809/00026VEN00276/11/2008 0:00Order PO00027
      PINV/0809/00027VEN00277/14/2008 0:00Order PO00028
      PINV/0809/00028VEN00067/11/2008 0:00Order PO00029
      PINV/0809/00029VEN00047/11/2008 0:00Order PO00030
      PINV/0809/00030VEN00047/11/2008 0:00Order PO00031
      PINV/0809/00031VEN00047/11/2008 0:00Order PO00032
      PINV/0809/00032VEN00047/11/2008 0:00Order PO00033
      PINV/0809/00033VEN00187/29/2008 0:00Order PO00035
      PINV/0809/00034VEN00436/20/2008 0:00Order PO00036
      PINV/0809/00035VEN00048/8/2008 0:00Order PO00038
      PINV/0809/00036VEN00441/1/2009 0:00Order PO00039
      PINV/0809/00037VEN00047/24/2008 0:00Order PO00040
      PINV/0809/00038VEN00107/12/2008 0:00Order PO00034
      PINV/0809/00039VEN00457/30/2008 0:00Order PO00043
      • Satish_Kumar's avatar
        Satish_Kumar
        Icon for Helper V rankHelper V
        9 years ago

        BhaveshPatel on the basis of No_ feild i have to join these table together to summerize the amount feild.