How to Join Multiple Tables in Zoho Analytics by Column Name
- For this example, we’ll be joining the ARCUS sample table with the ARIBH sample table by the IDCUST column on both tables. Please note that to column name on each table don’t need to match.
- Click on Create > Query Table in the left sidebar
- By default, you will see a sample query:
- Suppose you have two sample data tables, ARCUS and ARIBH, and they both have a matching column called IDCUST. Your goal is to join the ARCUS and ARIBH tables on their IDCUST columns. You only want to see the records from the ARIBH table, so this is a left join SQL query.
- Here is a sample query you can type in:SELECT
ARIBH.”DATEINVC” AS “Invoice Date”,
ARCUS.”TEXTSNAM” AS “Name”,
ARIBH.”IDCUST” AS “Customer ID”,
ARIBH.”IDINVC” AS “Invoice ID”,
ARIBH.”AMTNETTOT” AS “Net Total Amount”
JOIN ARCUS ON ARIBH.”IDCUST” = ARCUS.”IDCUST”
ORDER BY ARIBH.IDCUST ASC,
- Click Execute Query and make sure you’re on the Executed Query tab to view the result:
- Let’s break down what each part of the query does:
- The SELECT keyword chooses specific columns from the table
- FROM specifies which table you’re pulling the data from
- JOIN specifies how the tables will be joined, here, by their IDCUST columns
- ORDER BY specifies the order in which the records appear
- ASC means to show records from the smallest to largest (going from top to bottom on the executed result). ORDER BY ARIBH.IDCUST ASC means to show the customer IDs, ordered from smallest to largest, with smallest being at the top of the table, and largest at the bottom.
- DESC means to show the records from largest to smallest. Sorting by ARIBH.”DATEINVC” DESC means, show most recent records first.
- Here are some close-up screenshots of the SQL query and the executed results table: