SQL - Connecting to Multiple Tables

Sometimes you will need to select data from two or more tables to make the result complete. This is called a JOIN. You need to find a common reference between the two (or more) tables in order to make a JOIN complete, so you can tell the system how to connect the records between each of the tables.

Using the SQL Wizard

If more than one table is selected on the first page of the SQL Wizard, pressing Next will take you to the page to join the two tables. The tables will be displayed under Table and Related Table/Query. Click Create/Update Join to select the fields to join the two tables. A window will appear with three sections:

Tables - This displays the two tables trying to be joined.

Join Columns - This is where you can select the common fields to join the two tables. roomMaster has integrated logic to automatically populate the keys for commonly joined tables. If there are common fields between the tables, they will appear in the uppermost field of each column. If common fields cannot be determined by roomMaster, use the drop down box to display the fields for that table. Select a field from each list that relates to the same field from the other list. For example, to join tables FOLIODT and FOLIOHD, search each list for the field NUMBER. In both these tables this is the Folio Number field.

Note: The joining fields in two different tables do not always have the same name. Review the description of the field on the first page of the SQL Wizard under each table to determine if they are common fields..

To remove the join columns selected by roomMaster or manually, click the Clear Join Columns button.

Join Type - This is where you can determine which information will displayed based upon the join between the tables.

• Only include rows where the joined fields from both fields exist - Selecting this option will only return information where both joined fields have information in them.
• Include ALL rows from 'Table X' even if no record match in 'Table Y' - Select this option if you want to display all the rows in Table X even if there are no matching fields in Table Y.
• Include ALL rows from 'Table Y' even if no record match in 'Table X' - Select this option if you want to display all the rows in Table Y even if there are no matching fields in Table X.

Using the example from below, if ROOMS is Table X and FOLIODT table Y, selecting the first option would return only rooms that had guests in them. The second option would return ALL the rooms whether they had guests in them or not and the third option would return all the guests and the room numbers they are in.

Using Free-Form SQL Syntax

Joins

A join is an operation that combines the rows in tables by comparing the values in specified columns. Tables can be 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.

The following is an example of joining the FOLIOHD and FOLIODT files:

SELECT * FROM FOLIOHD JOIN FOLIODT ON FOLIODT.NUMBER = FOLIOHD.NUMBER

Other types of JOINS are INNER and OUTER joins. In the above examples, there must be at least one match with both files being joined. If there is no match, then no record will appear in the results. There may be times when you want to include the results from either of the tables, regardless of a connection being made between the two tables.

For example, say you want to pull up all the records in the ROOMS file and connect to the corresponding FOLIOHD when there is a person in the room. If the room is empty, you still want to display the results and just not include the name of the guest in the room.

If you were to use the following statement, you would only get back rooms which were occupied, as there must be a connection between the two tables. Even though there are say 30 rooms, and 10 are occupied, only 10 of the 30 records create a match in both tables, so only 10 records would be displayed.

SELECT ROOMNUMBER,FOLIOHD.LASTNAME 

 FROM ROOMS,FOLIOHD 

 WHERE ROOMS.FOLIO = FOLIOHD.NUMBER

OR

SELECT ROOMS.ROOMNUMBER, FOLIOHD.LASTNAME

FROM ROOMS
JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER

Now, if you wanted to display all the ROOMS regardless of a match in the FOLIOHD file, you would simply change the following statement to a LEFT OUTER JOIN. This simply tells the system to pull up all records in ROOMS regardless of a connection or not. The "LASTNAME" field will simply be blank when there is no connection.

SELECT ROOMS.ROOMNUMBER, FOLIOHD.LASTNAME

FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER

Connect to other tables to display formatted information:

For example: The following SQL Statement will display the userfield description rather than just the userfield code by connecting to the VIEW_USERFIELD1 table to the FOLIOHD table based on USER1 (userfield 1).

SELECT firstname as "First Name", lastname as "Last Name", Description FROM foliohd,view_userfield1 where view_userfield1.userfield = foliohd.user1

For more information on available VIEWS to retrieve formatted values, see Report Writing Views.