Database view joining a referenced field returning 0 results

[Renee-17]

I am trying to create a database view joining sc_request table with xyz_user table.  Note that the xyz_user table extends sys_user table.  The u_user column in the sc_request table is a Reference field that references the xyz_user table.

In the database view, the sc_request is prefixed with req and xyz_user is prefixed with usr. 

The join is as follows:

• req.u_user = xyz.sys_id

Also tried

• req_u_user = xyz_sys_id

In terms of query order I've placed the sc_request table first and placed the join in xyz_user which is called second.  I also tried calling xyz_user first and placing the join in the sc_request where clause which is called second.

Any insight will be greatly appreciated.  I've been able to join other tables with success but not these 2 tables.

[Renee-17]

Hi AshishKM,  here is a screenshot.  

Thanks Brad for pointing that out.  It was a typo on my part.  I was using either the usr.  or usr_

[Renee-17]

I don't know if something happens to sys_ids in cloned environments.  The environments I'm working in a Dev environment that was cloned from Prod.  I would think everything is completely replicated but wanted to mention.

[Renee-17 · Accepted solution]

I believe I found the issue.  It may have been related to the fact that I didn't have the columns that I was querying off of listed.  Sometimes, you can be prompted with this error but in this case I wasn't but added the columns and started to see results instead of an empty result set.