Solved

Avoiding duplicate values when combining two views SQL

  • 25 May 2023
  • 4 replies
  • 103 views
H
Rank
Userlevel 1
Badge +5

Hi,

    I have the below two tables with the mentioned fields and sample rows. Table GL_Ledger X has got a lot of actual details down to GL rows while table Budget Y has got budget amounts by Activity_Seq. I need to come up with one report where I have all fields of table X and one additional field for “AMT” coming from budget using the common filed Activity_seq.

 

Two issues in my result:

  • Duplication of budget amounts (in A1 rows).
  • Missing budget for A3 and A5

In other words, want to keep all details and rows of table X and pick up all records from table Y with no duplicates.

Any clues on how to solve this issue? Also if I will use union all, is there an easy way to copy all table X columns properties to table Y and how to do it if that is the only solution.

 

icon

Best answer by MMcK 25 May 2023, 12:20

View original

4 replies

M
Rank
Userlevel 2
Badge +10

Hi

You can achieve this with a union but you’ll need to define each of the columns that you want to show.  Something along these lines:

 

select activity_seq, amount, unit, sys_code, code from GL_Ledger_X

union

select activity_seq, AMT as amount, ‘’ as unit, ‘’ as sys_code, ‘’ as code from Budget_Y

   where activity_seq not in (select activity_seq from GL_Ledger_X where activity_seq is not null)

 

Note this does mean you won’t get the AMT from Budget_Y for any activities which exist in GL_Ledger_X but it wasn’t clear whether you needed that or not.  If you do, then you’ll have to do a view combining both tables and then query that with a group by to sum the amounts.

Hope this helps.

H
Rank
Userlevel 1
Badge +5

Thanks MMcK, actually i am looking for the below result where i will have all data from table X and all Data from table Y as per below.  I also expect the below blanks as null. your help is much appreciated

 

regards

Housseiny

 

 

M
Rank
Userlevel 2
Badge +10

Hi

In that case the query is slightly easier, you should be able to use

select activity_seq, amount, unit, sys_code, code, NULL as AMT from GL_Ledger_X

union

select activity_seq, NULL as amount, NULL as unit, NULL as sys_code, NULL as code AMT from Budget_Y

 

You just need to align the columns for the union.

Hope this helps.

H
Rank
Userlevel 1
Badge +5

Many thanks MMcK, it works as expected. Much appreciated!!!

 

Regards

Housseiny

Reply


Terms & ConditionsCookie settings