Left Join in creating database view

amiegross
Mega Contributor

Hi -

I'm trying to create a database view with a left join and I'm a little confused by the results I'm getting and questioning what "Leftjoin = true" means.  

What I'm try to create would be similar to writing a SQL query:

 

Select * from sys_user as u

left join u_integration_definition as id

on u.sys_id = id.u_user

 

What I expect is all records from the sys_user table.

I thought what I built below would get the results I wanted:

find_real_file.png

but I only get integration records (table u_integration_definition).  It's not until I flip the True/ False values on the tables where I would get all sys_user records. Why is that? I assumed that by selecting LeftJoin = true on the sys_user table, the sys_user table is the left table. I am now confident I have no idea what "Left Join = true" means. 

 

Thank you!

5 REPLIES 5

Harish Ragz
Kilo Guru

Once check the below link. It may help you.

Add a table to the database view

Thank you for your reply.

So, I read this and I still feel that my initial attempt (image in my original comment) should have given me my expected results.

 

  • Selecting Left join causes the left-hand table in the database view to display all records, even if the join condition does not find a matching record on the right-hand table. Select this check box for view tables that specify a Where clause. Selecting Left join for view tables without a Where clause does not affect the query. >>> Left Join = true is set on the sys_user table

 

  • Joined tables are ordered left to right from lowest to highest Order values. >>> Order on sys_user table = 100 (the lowest value)

 

Could you help me understand why my database view doesn't work?

 

I am also facing the same issue.Can someone assist?

I am also encountering the same, till I discovered the same "swapping the left join check box is actually working"

It would have been great, if we could somehow view the sql query that is getting generated while joining the tables.