Database View - Add Same Table Twice?

[Elizabeth26]

Can you add the same table twice within a database view?

We tried doing this but with different variable prefix and where clause and played around with left join true/false but cannot get it to work with the same table.

Wondering if we are doing something wrong or if this is not possible?

[Community Alums]

This is possible.  We have done so successfully.  What is your use case?

[Elizabeth26]

The use case is we have a report and the customer wants to have a relationship between a case number and role field but the same field in 2 different ways. 

The report ties together multiple tables through a database view. They want to report out on one of the tables using the same field in 2 different ways.

We have a field that's called 

"Role" and that contains multiple roles like customer support, analyst, manager etc. 

We need to find a way to tie together manager to customer support. 

So, in a row I'd need a case record/number that's associated to a manager and then customer support. I need a way to develop a relationship between them based upon the case number of the parent case. 

Here's what we tried 

Table: Case Variable Prefix: Case Where clause: none Left Join: false

Table: Involved Variable Prefix: Mgr Where Clause: role = 'Mgr' && mgr.parent_case= case.sys_id

Table: Involved Variable Prefix: Cust Where Clause: role = role = 'Cust' && cust.parent_case= case.sys_id Left Join: true

I can get it to work with case and 1 involved but not 2