Using multiple databases or tables (Video - 4:06)
Overview
Joins are basic functions of databases that enable you to merge the contents of two different tables and/or databases into a single view based on a common field or fields between them (that is, the join condition).
For more information on database joins please review the following:
About Table and Database Joins
Join types
There are four join types available in BarTender: Inner, Left Outer, Right Outer, Full Outer.
In BarTender, an inner join is the only join type supported when you are using data from multiple databases. In addition, full outer joins are not supported for Excel, Access, and MySQL databases.
Creating a join
Creating a database join in BarTender differs depending on whether the tables are from one database, or if the tables come from separate databases. The examples below include sample database files to download and use while working through the exercise.
Joining tables from the same database
Note: If you are using tables from different databases, then the steps are slightly different. Please proceed to the example below Joining tables from separate databases.
This example will set up a join using two tables from the same Excel file. Since they are tables from the same Excel file we can perform Inner, Left Outer, Right Outer joins, but not a Full Outer join.
Downloads
Database1.xlsx attachment
- Place Database1.xlsx in C:\Database Join Test\
- Create a new BarTender document
- Click File > Database Connection Setup ..
- Select Excel and click Next
- Browse to C:\Database Join Test\ and select Database1.xlsx. Click Next
- Add Table A and Table B to the Tables To Use list. Click Next
- The wizard will automatically create a join condition. Since both of the tables are from the same Excel file we can do an Inner, Left Outer or Right Outer join. Click Inner Join and change it to Left Outer Join
- Click Test and you should get a new record set
- Since this is a Left Outer join, and Donna exists in the "Left table", Donna is included in the new record set, however, since Donna doesn't have a value in the DepartmentID field, the DepartmentName field is blank for that record
- Close the Test record set. Click Finish to complete the join
- Click OK to close Database Setup
- Save the label
Joining tables from separate databases
This example will set up a join using one table from an Excel file, and one from a CSV text file. Since they are not tables from the same database, we can only set up an Inner join.
Downloads
Database1.xlsx
Table B.txt
- Place Database1.xlsx and Table_B.txt in C:\Database Join Test\
- Create a new BarTender document
- Click File > Database Connection Setup ..
- Select Excel and click Next
- Browse to C:\Database Join Test\ and select Database1.xlsx. Click Next
- Add Table A to the Tables To Use list. Click Finish
- Click the New Record Set icon in the lower left
- Select Text File and click Next
- Browse to C:\Database Join Test\ and select Table B.txt. Click Next
- Set Encoding to Windows' System Default, and Field Separation to Comma. Click Next
- Select The first row is a header containing field names. Click Finish
- Click <Left Operand> and select Database 1.'Table A$'>DepartmentID
- Click <Right Operand> and select Table B.Table>DepartmentID
- Select Record Browser (Joined) to view the new record set
- Since it is an inner join, only records with a Department ID in both tables are included in the new record set
- Click OK to close Database Setup
- Save the label