...
Clicking the ‘SQL’ tab will display the SQL executed to get this result set.
...
...
The default join type is Inner, but you can also create Left or Right (Outer) joins.
...
Selecting a ‘left’ join for example would change the SQL generated to.
...
‘Left’ is the table to the left of ‘JOIN’ immediately after ‘FROM’.
‘Right’ is the table specified in the JOIN statement, i.e. to the right.
In both a left and right join it will always return the rows where there is a match between the two tables, the left and right logic simply determines whether it will also include items where there is no such match.
A left join would return all rows from the left table, even if there were no matches on the right table, in this example all ukpostcodes even if they didn’t have a CUS_POSTCODE.
A right join would return all rows from the right table, even if there were no matches on the left table, in this example all CUS_POSTCODE, even if they didn’t have any ukpostcodes allocated to them.
The default option of inner join will only return rows that match on both tables. For example, if you had a table ‘AccountManagers’ and a table ‘Sales’ and you wanted to show sales per Account Manager in the dashboard including Account Managers with no Sales then you would need to use an outer join, all Account Managers would show and Sales figures would also be shown where there was a match. Whether this was a left or right join would depend on the table order as described above.
The default inner join would only return Account Managers who had Sales and their Sales figures.
Bear in mind that Outer joins can be more ‘expensive’ (database performance wise), you should only use them where there is a genuine need.
Which Joins will it use?
The dashboard will resolve the minimum joins it needs to use to connect the objects you have placed in a chart, bearing in mind the Table Priority. For example, if you had the following Meta Data.
...
A chart using an object from ‘ukpostcodes’ and an object from ‘nr_customers’ would only resolve the join between those two tables.
If on the other hand your chart had an object from ‘ukpostcode’ and an object from ‘nr_sales’ then it would resolve the join from ‘ukpostcode’ to ‘nr_customers’ and the join from ‘nr_customers’ to ‘nr_sales’.
The same would apply if your chart had objects from ‘ukpostcode’ and ‘nr_customers’ but you also had ‘nr_sales’ as a category object; when the chart SQL was created initially it wouldn’t join to ‘nr_sales’ , but once a category object on the ‘nr_sales’ table had been selected that table would also be joined on the underlying chart SQL.
| Info |
|---|
Please refer to this page for more information on table priorities. |
Forcing a Join / Referencing Fields on Other Tables
There is nothing to stop you in the object ‘Select’ statement referencing a field or fields that doesn’t exist in this table, but on another one that it is joined to (although you would have to set this up manually).
However, if you then built a chart that only used objects from the one table piDashboard wouldn’t automatically resolve the join to the other table and therefore our object and therefore the chart would fail. The dashboard has no way of knowing that one of the objects references a field held on another table.
Example
...
This object on the order detail line (POPOrderReturnLine) table is referencing a column on the order header table (POPOrderReturn), specifically DocumentTypeID.
If you were to build a chart that included this object but only looked at the order detail line table then it would fail because it has no notion of how to find POPOrderReturn.DocumentTypeID.
In this scenario you need to ‘force the join’ to POPOrderReturn, and generally the easiest way to do this is to add a filter from POPOrderReturn to your chart. This will then force PiDashboard to resolve the join to POPOrderReturn and it will then be able to find POPOrderReturn.DocumentTypeID. This could be a ‘nonsense’ filter such as Order Status <> ‘Micky Mouse’ or it could be something slightly more sensible such as Order Year > 1980, it doesn’t really matter as long as it’s not going to actually restrict the orders you want; you are only doing it to enforce the join.
Deleting a Join
To delete an existing join, click on the header of a table (the grey bar at the top of the table that shows the name of the table).
...