When connecting to a big data store or RDBMS data source, you can only select a single table of data. If you would like to select multiple tables, you must use Custom SQL or the SQL Designer. You can use these options to create joins between tables, so both datasets are included in your connection. This article will discuss how to use the SQL Designer to join multiple tables.
The SQL Designer is structured similarly to the Query Blender and provides an easy way to create joins between tables. If you are experienced with writing SQL, you can use the Custom SQL option to create joins instead. This article will discuss how to use the SQL Designer to join multiple tables.
- In the Info step of the Data source Builder, click on the Select button underneath table. If you have not connected to a big data store or RDBMS data source yet, see Connecting to Relational Database Management Systems and Connecting to big data stores.
- Click on the Designer tab to open the SQL Designer.
- Click the Launch button.
- Select two or more tables from the Select a table drop-down list.
Note: The tables you select must have a join key or common record to be joined.
- Click and drag from one half-circle on the sides of one table's box to a half-circle on the sides of another box. You will automatically be redirected to the Join definition window.
- In the Select a field drop-down list, select the attribute containing common values between the two tables.
- Click on the = icon to choose how the fields are related. By default fields are automatically equal to each other, but if you are joining around dates, you can use greater than or less than operators to exclude date ranges (see Creating join conditions).
- Click Add condition.
- Select the join type. You can select from three different join options. For information on the different join types, see Selecting a join type.
- Click Ok. BIME will then check to ensure that your join is valid, and you will be redirected to the SQL Designer.
- Click Ok again to finish joining your tables.
- The Designer tab will now show you the SQL query for your join. If you want to edit the join, you can launch the SQL Designer again or type directly into the query.
- Click Ok to finish creating your join.
- To the right of the Select button, you will see the first few lines of the query produced by the SQL Designer. You might see more or less lines, depending on your browser window size.
You have now joined the two tables. In the Schema step of the Data source Builder, you will be able to see all imported elements from both tables.