Skip to content

Joining Tables

 

The information in a database is typically structured in a way that prevents duplication of information.

For example, customer information is only stored in one table and then linked to transactions on the Primary Key. So if you want to create a query that displays transaction detail, you would need to join the Customer and Transaction table, because the transaction table does not contain any customer information, it only contains  a link to the Customer table.

There are many options for joining tables in SQL, but you need only consider two options which generally provide the functionality to extract just about any information from QuickEasy:

  • join - This is used when there is a guaranteed link to the table that is being joined. The Child record must have a corresponding Master record. eg. joining a Transaction Header to a Transaction Item - Items can't exist without a Header. A left join would also work in this scenario, but it would be a lot slower in a large database.
  • left join - This is used when there MAY be a link to the table that is being joined. eg. when joining Rep details to a Transaction - some Transactions may not be linked to a Rep. If a join was used, the Transactions which are not linked to Reps will not be displayed.

When joining tables, we need to consider the order in which they are joined. By limiting ourselves to the join and left joinoptions, we need to always start with the detail table when joining tables that have a Master-Detail relationship. For instance, if we wanted to show data from the Customer, Transaction Header as well as the Transaction Items, we would start with the Transaction Items, join the Transaction Header and then join the Customer table.

Aliases

When multiple tables are joined into a query, you need to identify the table to which each field in the query is linked. eg.

select
VATITEMS.*,
LEDGER.*,
CUSMAIN.*
from VATITEMS
join LEDGER on VATITEMS.TRANSID = LEDGER.TRANSID
join CUSMAIN on LEDGER.COMPANYID = CUSMAIN.CUSID

You can also do this by assigning an alias to each table which can in turn be used to identify which tables the fields are linked to. The above example would then look like this:

select
V.*,
L.*,
C.*
from VATITEMS V
join LEDGER L on V.TRANSID = L.TRANSID
join CUSMAIN C on L.COMPANYID = C.CUSID

Conditions

When joining tables in a Query, you also need to specify the joining conditions. ie. Which fields in each table will create the link. In most cases you simply specify the fields that create the join. In certain cases, there may be additional conditions that must be added. eg. The query to join the selected Estimate quantity to an Estimate item.

select
*
from VATITEMS V
left join QUOTEQTY Q on V.VATITEMID = Q.VATITEMID and Q.STATUSID = 1