Create a database view to show records from table a but not exists in table b using left join

[jarretjo]

Hi, 

The requirement is to create a Report which will display "Demands" (dmn_demand  prefix:d) which DO NOT have a referenced record in another table called "Demand Estimation" (u_dmn_estimation prefix:r).

The link between them is: d.sys_id = r.parent

Since this data should be available to a Business User I opted for a Database View. (Any alternative suggestions welcome!)

(This is a lot more complicated than it should be! Would be a better and quicker option to write the SQL and have that saved as a View - my 2 bucks worth! Documentation for Database View also stops at the Basic Level!)

I got the idea that the "Where" clause in Database View is only for joining tables. You cant add a filter but the filter can be done after the data is in the view.

I was expecting the following configuration to display all the Demands and only those that matched. (LEFT join).

However it doesnt, it seems to ignore the LEFT join and only bring back matching. I think Ive tried every combination there is to get it to work without joy, but there must be something Im missing.

Anyone know why its only returning matching records? Thanks 🙂

[Aoife]

Is you custom Demand Estimation table a child table of Demand?  I only ask because of the parent field which usually indicates a child table so there would be no need to do a view, you can simply query based on the Class and the value of Parent being empty or not empty.

Aoife

[jarretjo]

Hi Aoife, Its a referenced table! 

Joe

[palanikumar]

Set the Left join on estimation table and leave it as false in demand table and see whether the issue is resolved

Thank you,

Palani

[jarretjo]

Hi Palani,

Tried that and it returns the same amount of records regardless whether the left join is on the estimation table or the demand table or just left out.

Thanks Joe