I need to implement the equivalent of a right join in a View in Creator and then display with grouping. Here is an example. Imagine two tables, Supervisor and Employee. Each has a primary key and a Name. There is a one to many relationship such that an employee may have only one supervisor, but a supervisor may have many employees. This is implemented in forms in Creator my adding a lookup table in the Employee form in order to choose the Supervisor.
Now imagine that some Supervisors have no employees.
I want a view that shows all Supervisors plus employees that have supervisors.
If I create a view based on Supervisor, I get all supervisors and no option to link into the employee table to get any employee information.
If I create a view based on Employee, I can show employees and then group by supervisor but I only get supervisors who have a match in Employee, as expected.
I end up with what is "Select * from Employee inner join Supervisor . . ."
What I really want is "Select * from Employee right join Supervisor . . ." such that I see all supervisors.
I also might want a view that shows all employees and all supervisors and uses the equvalent of an
outer join.
This is a real time application, so I cannot import data and use Zoho Reports.
I have thought of writing a script that populates a form with such data and then displays the form, but that seems really complex at the moment for such a simple sort of display. Any ideas?