I am trying to pull data from multiple tables using a single select query and there are duplicates in the results. I am sure it is how I am using Join but can’t figure out why. The tables are as follows:

T1
WE ST WTL WGC WGFT
2024-01-01 111 159.44 56 0
2024-01-01 222 385.45 84 26
2024-01-01 333 611.46 12 15
2024-01-01 444 837.47 59 0
2024-01-01 555 1063.48 88 10

T2
WE ST OETL OEGC
2024-01-01 111 1254.55 45
2024-01-01 222 4589.32 89
2024-01-01 333 7924.09 74
2024-01-01 444 11258.86 21
2024-01-01 555 14593.63 65

T3
WE ST OCTL OCGC
2024-01-01 111 126.75 12
2024-01-01 222 459.86 48
2024-01-01 333 174.56 79
2024-01-01 444 859.45 25
2024-01-01 555 684.97 42

T4
WE ST ITTL ITGC
2024-01-01 111 148.59 74
2024-01-01 222 65.70 22
2024-01-01 444 489.59 95
2024-01-01 555 89.59 12

T5
WE ST ICTL ICGC
2024-01-01 111 187.56 39
2024-01-01 333 389.56 57
2024-01-01 555 452.11 46

T6
WE ST DTL
2024-01-01 111 51.91
2024-01-01 333 114.85
2024-01-01 555 57.48

Query Looks Like This:

     Select 
   [T1].[WE]
  ,[T1].[ST]
  ,Coalesce ([T2].[OETL]-[T3].[OCTL],[T2].[OETL],[T3].[OCTL],0) as OETL
  ,Coalesce ([T2].[OEGC]-[T3].[OCGC],[T2].[OEGC],[T3].[OCGC],0) as OCGC
  ,ISNULL ([T3].[OCTL],0) as OCTL
  ,ISNULL ([T3].[OCGC],0) as OCGC
  ,ISNULL ([T4].[ITTL],0) as ITTL
  ,ISNULL ([T4].[ICGC],0) as ICGC
  ,Coalesce (OETL+ITTL-OCTL,OETL,ITTL,OCTL,0) as ATGS
  ,Coalesce (OEGC+ICGC-OCGC,OEGC,ICGC,OCGC,0) as ATGG
  ,ISNULL ([T3].[OCTL],0) as OCTL
  ,ISNULL ([T3].[OCGC],0) as OCGC
  ,ISNULL ([T5].[ICTL],0) as ICTL
  ,ISNULL ([T5].[ICGC],0) as ICGC
  ,Coalesce (OCTL+ICTL,OCTL,ICTL,0) as ACS
  ,Coalesce (OCGC+ICGC,OCGC,ICGC,0) as ACG
  ,Coalesce ([T1].[WTL]-[T6].[DTL],[T1].[WTL],[T6].[DTL],0) as WTL
  ,ISNULL ([T1].[WGC],0) as WGC
  ,ISNULL ([T1].[WGFT],0) as WGFT

  From dbo.T1
  Join dbo.T4 on dbo.T1.ST = dbo.T4.ST
  Join dbo.T6 on dbo.T1.ST = dbo.T6.ST
  Join dbo.T2 on dbo.T1.ST = dbo.T2.ST
  Join dbo.T3 on dbo.T1.ST = dbo.T3.ST
  Full Outer Join dbo.T5 on dbo.T1.ST = dbo.T5.ST

    
      where [T1].[WE] between '2024-04-18' and '2024-04-24'


     order by ST

I am using a full join on T5 because not all ST’s will have information from this table.

I was expecting the following a single line result for each ST with information for each table, something like this:

WE ST OETL OEGC OCTL OCGC ITTL ITGC ATGS
2024-04-24 111 1254.55 45 126.85 12 148.59 108 1276.29
2024-04-24 222 4589.32 89 459.86 48 65.70 108 4195.16