How to create a full join in database view

[oacp]

Hi,

I am trying to create a full outer join in a database view.

The reason for this is that I want to display all records from three different tables for reporting purposes but there is no link that I can create in the 'where' clauses.

I just want all records from all three tables displayed with '(empty)' displayed in the columns where information is not present for that table.

I am not sure how to do this since I can only find the option to create a left join and as mentioned do not have much to link in the 'where' clauses. I have looked on the Wiki but it only provides information on left joins.

Thanks in advance!

[acirovic1]

Just set the Left Join property to "true" on both tables.

[rrataj]

that will still give a left join, look at the counts of tables and the count of the join produced in the view,

Servicenow uses MySQL as database and MariaDB as a driver / interface

looking at MariaDB there is only a possibility of doing a cross join (eventually then e.g. for Reporting define the joins in a filter ), and I do not see such possibility in the Servicenow UI

Please correct me if I'm wrong. 

[Rohan11]

In SNOW you can't do full outer join as in SQL (from my experience). But if they have same base table and you need to get the columns from the extended table not present on base table then you can do a sort of outer join. I had a requirement to present on the portal My tickets from different application but which were related by task table as base table.

DB view name - xyz

Base table - B1

extended table 1 - Ex1

extended table 2 - Ex2

---

---

---

In DB view XYZ add tables as below:

100 - B1 - variable name B11

!include sys_id in field list

*************************************

200 - B1 - variable name B12 - where clause B11_sys_id = B12_sys_id && (B12_<field1> = 'Ex1 identifier' || B12_<field1> = 'Ex2 identifier')

!include sys_id and <field1> in field list

*************************************

300 Ex1 - variable name ex1 - where clause B11_sys_id = ex1_sys_id left join is true

!include sys_id in field list

*************************************

400 Ex2 - variable name ex1 - where clause B11_sys_id = ex2_sys_id left join is true

!include sys_id in field list

*****************DONE**********************************

[Chris Sanford1]

I think this might work for a full outer join of two tables:

1. Create a database view of table1 left joined with table2
2. Create a database view of table2 left joined with the database view from step1

For the third table repeat steps 1 and 2 where "table1" is your database view from step 1 and "table2" is your third table.

Haven't tried this but just a thought.

Another thought, you still do need a where clause for a full outer join, unless you want a complete Cartesian product. That would be very costly on performance. Three tables with just 100 records each would put you at 1,000,000 records. The formatting would also be bad with all the blank columns. A better recommendation, based on my understanding of your requirements would be a homepage with three separate gauges on it for the three tables.

I see this thread is very old but someone recently replied to it so it caught my attention, as I have thought about doing full outer joins in the past.