Symptom
This is a foundation article, provided to explain the concept of a join. Recommended to provide assistance for understanding joins in Reporting tools.
Environment
* Workforce Analytics
Resolution
What is a Join?
A join is a simple connection of two items. In relation to data, it is the joining of two data tables (sets of information) via a common set of data (information).
Think back to Venn Diagrams, which a lot of us learnt about in Maths at school.
Circle ‘A’ is one set of information. Circle ‘B’ is another set of information. Intersection ‘C’ is the common information found in both sets.
This is the same principle of a join between two data tables. You are finding ‘C’ to join the two tables.
In the examples provided below, there are two tables – CUSTOMER and ORDERS.
CUSTOMER
CUSTID NAME AGE ADDRESS
1 Rodger 25 Annondale
2 Cillian 36 Fairfield
3 Alex 44 Moraben
4 Paul 56 Kingsley
ORDERS
ORDERID DATEORDERED CUSTID AMOUNT
65 23/03/2015 1 500.00
66 23/03/2015 2 2000.00
67 24/03/2015 4 5000.00
For this example, to join the CUSTOMER and ORDERS tables, you would use the ID from the CUSTOMER (CUSTID) table and (CUSTID) from the ORDERS table to join the two sets of data.
Selecting the "fields" you require: (A field being a piece of information/data)
* CUSTID - Customer ID from CUSTOMER table
* NAME - Customer Name - from CUSTOMER table
* DATE - Data of Order - from ORDERS table
* AMOUNT - Amount - from ORDERS table
You would see the following result:
CUSTID NAME DATE AMOUNT
1 Rodger 23/03/2015 500.00
2 Cillian 23/03/2015 2000.00
4 Paul 24/03/2015 5000.00
This output is the combination of the two tables, by working with CUSTID to combine the information, and selecting the required fields.
Going back to the Venn diagram above, CUSTID from the CUSTOMER table and CUSTID from the ORDERS table, would the the 'C' - common set of information
Why use a JOIN?
Working with JOINs creates more flexibility in the data you can achieve and share via reports. It allows you to work with smaller tables (sets of information) to allow for greater speed in processing information.
One thing to be aware of in regards to JOINS, is that there are different 'types' of joins.
For more informaton on the 'types' of joins, refer to this website:
http://www.tutorialspoint.com/sql/sql-using-joins.htm - SQL Join Types.
Keywords
KBA , LOD-SF-WFA-DES , Report Designer , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, YouCalc, ORD) , How To