Duplicate records in database views.

SHANKAR RAMAVAT
Tera Contributor

yesterday

Hello Everyone,

I have created a database view for joining Business application(cmdb_ci_business_app), Plan(sn_bcp_plan) reference field to business application, Event(sn_recovery_event) reference field to business application. Here the business application is parent table of plan and event tables.  There is no relation between plan and event table.

SHANKARRAMAVAT_0-1757413908457.png

The issue I'm facing here is we are getting the duplicate records.

for example, In the first application number(APM1000000) we are having 2 event records(short description) and one draft plan record, The business application should have only one draft record. 

SHANKARRAMAVAT_1-1757424064713.png

Can someone please help me to remove these duplicate records. Let me know if you have any queries. 

 

Thank you.

0 Helpfuls
  • 98 Views
1 REPLY 1

G Ponsekar
Mega Guru

yesterday

Hi @SHANKAR RAMAVAT ,

 

The duplication in your database view is caused by a "Cartesian product" effect. This happens because for each Business Application, the database view returns a record for every combination of related Plans and Events. Since there is no join condition between the sn_bcp_plan (Plan) and sn_recovery_event (Event) tables, the number of records is multiplied. 
To correct this, you need to structure the database view to handle the one-to-many relationships separately. A single database view cannot reliably show all records from multiple, unlinked one-to-many tables without creating duplicates. The best solution is to create two separate database views: one for Business Applications with Plans, and one for Business Applications with Events. You can then use both views for reporting
 

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

 

Thanks, GP

 

0 Helpfuls