Skip to main content

SQL Wizard - How are these tables related

If you have selected data from more than one table, you must define how these table(s) are related to each other. A join is an operation that combines the rows in tables by comparing the values in specified columns.

Tables are joined using join conditions. A join condition is simply a search condition. It chooses a subset of rows from the joined tables based on the relationship between values in the columns. For example, the following demonstrates the joining of two tables.

SELECT FOLIOHD.LASTNAME,FOLIOHD.ROOMNUMBER,ROOMS.BEDTYPE

  FROM FOLIOHD,ROOMS

    WHERE FOLIOHD.ROOMNUMBER = ROOMS.ROOMNUMBER

In the above example, we need to join to the ROOMS file to get the BED TYPE of each occupied room. The join condition is:

foliohd.roomnumber = rooms.roomnumber

This means, when the query reads each row in the FOLIOHD file, it should go out and find the corresponding BEDTYPE from the ROOMS file for that particular room number.

When creating a join condition, it is very important that the correct logical condition is created, otherwise, you would end up with incorrect results.

Join Type​

A join condition (by default) must match in all related tables, or the record will be skipped completely. This is called an "Explicit Join Condition". The query wizard allows you to select the Join Type and will create the proper Join syntax needed.

Set Primary Table​

The primary table is the table where the system will go first to start collecting all the data. This is usually the table where there are the most records and usually the table used in your primary WHERE clause. For example, if you wanted a list of all the invoices in the system along with the name of the company that belongs to invoice, your primary table would be the invoices - not the table which has the company names.

CITYDT - City Ledger Invoices

CITYHD - City Ledger names and addresses

If CITYDT were the primary table, the system will go out to all the CITYDT (Invoices) and grab a list of all the invoices that are in your WHERE clause (maybe all the invoices for this month) and grab all that data first. (This is the primary table or the primary first pass of the data). Once it grabs all those invoices, then it will go and get the Account Names (CITYHD) which belong to the invoices.

If CITYHD were the primary table, the system will go out to all the companies and see if there were any invoices for each company, one by one. Let's say you selected CITYHD as the primary table. Picture that you have maybe 1,000 city ledger accounts but when you selected all the invoices for the week, maybe only 8 unique companies did business with you that week. It would make more sense to grab all the invoices (CITYDT) that you had during the week and then getting the associated company names rather than looking to see if there are any invoices this week for all 1,000 companies (CITYHD). That is why CITYDT is the primary table and not CITYHD.

This online help is not designed to fully explain what you need to know to create a successful join. You should get a book on learning SQL to gain a full understanding of JOIN's.

It is important to remember that when you create joins, you must join tables on the same type of fields and fields which have meaning with each other. You wouldn't join the ACCOUNTS file with the ROOMS file since they simply don't have any relationship with each other. This would be the same as joining the RESERVE file with the ACCOUNTS file where the CHECKINDATE equals the ACCOUNT CODE. These two items have nothing at all to do with each other, so it would be an improper join and your query would simply not work.

To assist in creating the proper joins, roomMaster has described all keys so that join fields are automatically populated for all common links. Click here for more information on joining tables.

Once you have completed the Join requirements, press Next to specify which records to include in query