Left join is not working for database view

Vijay Talupula · ‎05-31-2017

Guys,

I have created a DB view for User Vs User Role table in my personal instance. I have turned on left join checkbox and it is working as expected.

But the same thing when I try in my client dev instance it is giving only matching records but not all the records. Please see the below screen shots for reference.

And it is giving 2511 records which is User role table count where as User table is having 26k+ records. I am not sure what I am missing here tough created similarly how I did in my personal dev instance.

Note: Both are Istanbul versions but personal instance is patch5 and client instance is patch3 instance. Not sure whether it is causing the issue.

Kalaiarasan Pus · ‎06-01-2017

Can you make these change and tell me if this is what you are looking for?

View table -> sys_user - order-100 - where-user_sys_id=role_user - Left join-True

View table -> sys_user_has_role - order-200 - Left join-True

View solution in original post

snehabinani26 · ‎05-31-2017

HI Vijay,

If you want all record of User table then change your where clause to usr_sys_id = role_user.

Hope this helps you

Vijay Talupula · ‎06-01-2017

I have already tried that but there was no luck

Kalaiarasan Pus · ‎05-31-2017

First thing, you do not need a database view to join these tables.

You can directly dot-walk the user field and get all the required details of the user using just sys_user_has_role table.

So, do we now need to debug this issue further?

Vijay Talupula · ‎06-01-2017

If I do like that I would get only the records available in User Roles table but I am looking to get all the User records with roles from User table. Thats the reason I am using database view Kalai. Hope you got my requirement clearly.