Help with Database View Where Clause

[Still Learning]

Hello SN Community,

I am trying to map three tables and create a database view to build a dashboard. The issue I am encountering is that the list view is empty when I attempt to try my database view.  There are more than 2,000 records associated with each table so the list should not be empty.  

The three tables I am mapping are: 

pm_project (left join),  project_status, and pm_project_task ... each table has a list of fields but I don't believe that is the problem. I'm thinking the issue is with my where clause.  Can someone please tell me what I am doing wrong?? My table mappings are below.

[Aman Kumar S]

SO basically, how it will work, left join works:

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

So if you want to show all the rows no matter what, you have to do left join from task and status table

You can refer to one example in SN docs:

https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/use/reporting/task/ex...

[Still Learning]

Thank you Aman!! This is great information!

[Aman Kumar S]

Glad to help 🙂

[Chandu Telu]

Hi 

Try below one

stat_project_number = prj_number

task_parent_number = prj_number

Thanks
Chandu Telu
Please Mark ✅ Correct/helpful, if applicable,

[Still Learning]

Thank you so much for your reply Chandu!! However, the where clause you suggested returned errors without taking me to the table view. But I really appreciate your response. Thank you!!