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

jarretjo
Tera Contributor

‎04-07-2022 07:55 AM

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 🙂

find_real_file.png

 

 

 

 

 

 

 

 

 

 

 

 

Labels:
Preview file
20 KB
0 Helpfuls
  • 1,694 Views
5 REPLIES 5

Ishu
Tera Contributor

‎10-31-2023 04:54 AM - edited ‎10-31-2023 10:25 PM

Hi,

 

You need to keep left join as true for the estimation table(which has field referencing to another table) and false for the demand table(which is being referenced). And also try with keeping the order value same for both, as it is working for me, with above configuration.

 

Regards,

Ishant

0 Helpfuls