Add customer table field to an existing Database view

[angelas]

Hello- I have a custom table - u_aws that when triggered a survey goes out.

I have created a database view for reporting (u_report). 

One of the fields on that DB view is TriggerID: Number (number of the record on  u_aws).

I now need to grab a field from u_aws (queue_name) for the database view.

I'm struggling on how to write this where clause. I keep getting an error.

I'm adding u_aws with prefix dbaws to the database view.

What should my where clause be and should it join left?

Thank you!

[Brad Bowman · Accepted solution]

This is difficult to follow without a picture of the existing working Database View, and with at least one custom table, but generally you only want a left join if you want to include all of the records from one of tables even if it doesn't have a record on the other table in the join.  The where clause needs to use a field on the new table that is a reference to a  one of the previous tables.

[Brad Bowman · Accepted solution]

This is difficult to follow without a picture of the existing working Database View, and with at least one custom table, but generally you only want a left join if you want to include all of the records from one of tables even if it doesn't have a record on the other table in the join.  The where clause needs to use a field on the new table that is a reference to a  one of the previous tables.

[angelas]

I could just add all the records from the custom table and not limit it to just the field I need, so the left join would be sufficient. The problem is no field on the custom table is referenced in the other tables in the db view except that TriggerID field = AWS# in custom table.

[Brad Bowman]

Here's a simple example with out of the box tables and fields.

This Database View will contain all fields from the incident and assessment instance tables.  The assessment Trigger ID is the incident sys_id, so that is my Where clause.  I want to see every incident, even the ones that do not have an assessment instance, so I have made this a left join.

[angelas]

So I already have the assessment instance table with a where clause for my other database view.

Could I add to the existing where clause?