Database Views

[MPNSC]

Anybody have any idea how to make the last 2 tables in my database views as unique records? So I have sn-hr_core_case table, question_answer and sc_multi_row_question_answer inside the database view. The last 2 tables reference sn_hr_core_case but I want to get unique records for each and not join them. What happens is I have 11 expected records in question_answer and 6 expected records in sc_multi_row_question_answer for an HR case but in database view I'm getting 66 records

[Ankur Bawiskar]

@MPNSC 

that's OOTB thing.

see this link

database view showing duplicate records 

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@MPNSC 

Thank you for marking my response as helpful.

As per community feature you can mark multiple responses as correct.

💡 If my response helped, please mark it as correct ✅ as well so that this helps future readers find the solution faster! 🙏

[KavinderS · Accepted solution]

A ServiceNow Database View is fundamentally a join. Once you include both child tables in the same view and both have multiple rows per parent, the database must return combinations of rows (that’s how joins work). ServiceNow doesn’t provide a “DISTINCT per joined-table” or “don’t join these two children” behavior inside a single database view; even ServiceNow community guidance for “SQL DISTINCT in a database view” points to grouping/reporting or GlideAggregate as the workaround rather than fixing it in the view definition itself.

you can’t have those two child tables in one DB view and also expect 11 rows + 6 rows separately. You’ll always get multiplication unless one side is 0/1 row per parent.
You can either create two views or create a report on child table.

[MPNSC]

Thank you all! just wanted to make sure it really was not achievable through database views. I'll go with custom page then to solve requirement