4 table problem

• kevinlall

  Newbie

  Points: 2

  More actions

  May 20, 2008 at 6:32 am

  #207411

  hello i have a little problem i hope that SQLjunkie can help i try for days to fix or get it to work but no luck:w00t:

  i have 4 table ARCASH,ARYCSH,ARMAST and ARYMST

  ARCASH is a current tran.

  ARYCSH is history Tran.

  ARMAST is current Tran.

  ARYMST is History Tran.

  what i want to do is

  UNION ARYCSH AND ARCASH

  UNION ARYMST AND ARMAST

  and make a relation link between the 2 union table on field "CUSTNO"

  now the problem is when i do this

  this is what i get

  the custno show up and the union data from the arcash/arycsh show up and the union data from arymst/armast the first record keep repeat it self this is what i mean

  CUSTNO----------PAIDAMT(ARCASH/ARYMST)------------INVAMT(ARMAST/ARYMST)

  ALL001 ----------11000------------------------------------- 12500

  CUS101 ----------900---------------------------------------- 12500

  CAR124 ----------700---------------------------------------- 12500

  GOE451 ----------4500--------------------------------------- 12500

  TOL003 -----------1587 ---------------------------------------12500

  ALL001 ------------25487------------------------------------- 12500

  ALL002------------- 5787-------------------------------------- 12500

  ALL001 -------------5465 --------------------------------------12500

  as you can see armast/arymst first record over and over

  how do i fix this or get it to work

  thanks

  kevin.

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  May 20, 2008 at 6:48 am

  #817816

  [font="Verdana"]I guess, you might have missed some join. Post table structure and sample data.[/font]

  MH-09-AM-8694

• Kevin-458339

  SSCrazy

  Points: 2717

  More actions

  May 20, 2008 at 7:04 am

  #817832

  Your union will return records from both tables unless you perform an aggregation on each table in the query for each query of the union.

  The other option which can be used is to place the records into a temp table and then apply the aggregations to the raw records.

  From your details we can not see your full selection criteria - but maybe a group by on the custno is required and then sum(AMT) columns

  let us know

  Thanks

  Kevin

• kevinlall

  Newbie

  Points: 2

  More actions

  May 20, 2008 at 9:00 am

  #817997

  thanks i will let you know i made a few views testing it now