Performing an inner join

Difficulty

Objective

You want to perform an "INNER JOIN" in 1010data, that is you want to combine the columns of two tables and only retain the entries that match.

SQL solution

SELECT * 
FROM sales_item_detail 
JOIN product_master 
ON sales_item_detail.sku=product_master.sku 
WHERE YEAR(date)=2016 AND groupdesc='FRUIT';

An INNER JOIN in SQL is the same as a regular JOIN. When two tables are joined, only the rows that match the criteria specified with ON are included in the result.

1010data Macro Language solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<sel value="year(trans_date)=2016"/>
<link table2="pub.doc.retail.product" col="sku" col2="sku" type="select">
  <sel value="groupdesc='FRUIT'"/>
</link>

Performing a JOIN or an INNER JOIN between two tables in SQL will result in a worksheet that contains only the matching rows between both of the tables. You can obtain the same result in 1010data by performing a link and select.

By specifying type="select", only the rows from the base table that have a match in the foreign table are retained. As shown here, you can nest a query inside the <link> that will be applied to the foreign table before the link occurs. Here, only groups that contain "FRUIT" in the description are selected from the foreign table, and this is used as the selection basis in the link.

However, unlike SQL, you need to be aware of which table is your base table and which is your foreign table. If the smaller table is your base table and the foreign table has multiple entries for one entry in your base table, only the first entry will be included in the results. You need to make sure either that the larger table is your base table, or that you use expand="1" if the larger table is your foreign table.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

<link>