Creating joins between tables

QUERY > Selecting > Creating joins between tables

Back to

Creating joins between tables

Relationships between tables increase the power of queries. In QUERY, relationships, or joins, are created automatically when multiple tables are added to the query builder area, if a primary key link or a check table link exists between the tables.

To improve return time for multitable joins, when a criteria is applied to a field in table, the criteria is also applied to that field in the other tables in the join. Therefore, the join is made only between the filtered records of the tables, not between entire tables.

Primary key links and check table links

A check table link exists when two tables are linked by foreign key fields. One table assigns some of its fields to be the primary keys of another table, called the check table.

A primary key join is possible when the primary keys in the tables have the same field name, and are of the same data type and field size. The primary is indicated by a key icon . Indexes are indicated by a blue inverted key .

Default joining of Primary and Index Keys

If a primary key and index key have matching data fields, the application creates automatic joins between the primary key and the index key. The tables must contain the same field name, field size, and be of the same data type.

NOTE: If primary keys are linked, index keys are not joined automatically.

User-created joins

In QUERY, you can create and remove joins if the administrator grants permission to do so. In addition to creating joins on regular tables, you can join cluster and pool tables, and views. You can also join a table to itself.

The administrator must grant permission for all joins. For more information, see Admin options.

To create a join between two tables

Drag a field from one table and drop it into the other.

All downloads from joined tables are by an inner join—all matching records are downloaded.

Important: If you use multiple tables, they must be joined. If the tables that you query are not joined, the query will not run.

To join a table to itself

Double-click the table twice to add the table and its alias to query builder area. For example, if the table is MARA, its alias will have the name MARA1.
To create the join, drag a field from the table or alias to the alias or table.

All downloads from joined tables are by an inner join—all matching records are downloaded.

Left Outer Joins

You can create left outer joins in the QUERY builder. In left outer joins, data is returned from one table even though there are no matching records in the second table. Note that outer joins can degrade performance.

Recognizing left outer joins in Winshuttle QUERY

You can recognize a left outer join in QUERY by the join line with an arrow pointing to the left table.

If two tables are added with left outer joins, the joining arrow may not appear. However, this does not affect the function of the join.

To convert to left outer joins

In QUERY builder, right-click on the joining link between the tables.
In the shortcut menu, click Convert to left outer Join.
A warning message appears to remind you that converting to a left outer join may degrade performance.

To remove or convert an outer join

Right-click the left outer join.
On the shortcut menu, click Remove or click Convert to INNER join.

Conditions to create left outer joins

From the outer joins only left outer join is supported.
Maximum of 25 tables can be joined that can include inner and left outer joins.
Only two tables can have left outer join between them in a query.
The table on right of a left outer join cannot be a right table of any other join.
For left outer join between two tables, no other type of join is allowed across any of their fields.
Any field in the right table that has a left outer join cannot be used as selection.
Any query with left outer join altered in previous version of QUERY may not generate accurate results.
The following conditions are applicable for creating new joins when a left join already exists:
- All entries will conform to left join conditions.
- Selecting some joins in the grid may disable other joins because of left outer join limitations.
- If the Winshuttle Function Module predates version 10.2 and you run a query with a left outer join, the run aborts.

To delete a join

Right-click the join line that you want to delete, and then click Remove.

Mismatched joins

Winshuttle Query allows joins between indexed and non-indexed fields, and also between fields of different character lengths. However, the information type, material, number, organization unit, and so on must be the same. Joins between other mismatched fields may be allowed, but no data can be extracted. In such circumstances, Query displays a warning that no data can be downloaded through the join.

The ability to join on mismatched tables and fields may be disabled or enabled for your organization by the Winshuttle administrator. For more information, see Administrator options.

Joining with InfoSets and SAP queries

You can use only one InfoSet/SAP query in the query builder area at a time. Joining of more than one InfoSet/SAP query with tables is not supported.

Also in this section

Data sources

Starting a query

Tables and the Winshuttle Data Dictionary

Adding items to and searching for items in query builder

Selecting fields

Saving a query

Recording transaction codes