Join the #BuildWithBuildAgent Challenge! Get recognized, earn exclusive swag, and inspire the ServiceNow Community with what you can build using Build Agent.  Join the Challenge.

Duplicate records in database views.

SHANKAR RAMAVAT
Tera Contributor

‎09-09-2025 06:32 AM

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
  • 263 Views
1 REPLY 1

G Ponsekar
Giga Guru

‎09-09-2025 06:41 AM

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