JOINS  

INNER JOIN

This is the most commonly used join in recorder. It matches rows between two tables based on supplied criteria. It is the method for linking tables together. The first example below joins the Survey and Survey_Event tables. This is the correct join to use in this situation because Survey_Events must always have a link to a Survey. Note that the example uses table aliases which are declared in the FROM clause the first time the table appears. (eg, SV is the alias for Survey and SE the alias for Survey_Event.)

Select SV.*,SE.*  FROM Survey SV INNER JOIN Survey_Event SE ON SV.Survey_Key = SE.Survey_Key

There can be as many joins as are needed to get to the data required.

Select SV.*,SE.* ,S.*  FROM Survey SV INNER JOIN Survey_Event SE  ON SV.Survey_Key = SE.Survey_Key INNER JOIN SAMPLE S ON  S.Survey_Event_Key  = SE.Survey_Event_Key

You can specify a logical expression either in a WHERE clause or as part of the join. Both example below return the same results with an INNER join. In the first example below the logical expression TDET.Preferred = 1 (which limits the output to just preferred determinations) is included in the JOIN and in the second example it is included in the  WHERE clause.

Select TOCC.Taxon_Occurrence_key, TDET.Taxon_List_Item_Key FROM  SAMPLE S INNER JOIN  Taxon_Occurrence TOCC ON TOCC.Sample_Key =  S.Sample_Key INNER JOIN Taxon_Determination TDET ON TDET.Taxon_Occurrence_Key = TOCC.Taxon_Occurrence_Key AND TDET.Preferred = 1

OUTER JOINS

Outer joins match rows between two tables based on supplied criteria but also return rows from the 'preserved' table for which there is no match. The preserved tables can be either the LEFT tabled, RIGHT table or FULL tables(both). The LEFT OUTER JOIN is the most commonly used with the RIGHT OUTER JOIN used occassionally. Note that  'LEFT' table is the first table mentioned and  in example 1 this is the Sample table.   

LEFT JOINS

 In example 1 below  the LEFT JOIN  returns all the Sample data even if there is no Location attached. The use of the logical expression LN.Preferred = 1 as partb of the join restricts the Location names to just the preferred Locations by filtering out the non-preferred when the join is applied.  Example 2  has the logical expression in the WHERE clause, but this does not work, because  the where clause is applied to all the records and those without Locations have Null in the preferred field. This problem is overcome in example 3 with the addition of the check for Null. However, the same effect can be achieved by Example 4 where the 'preferred' is covered as part of the join.   (Note the keyword OUTER  is optional the keywords LEFT,RIGHT and FULL imply an OUTER join).

1. Select S.Sample_Key, L.Location_Key, LN.Item_Name FROM Sample S LEFT JOIN Location L ON L.Location_Key = S.Location_Key
LEFT JOIN Location_Name LN ON LN.Location_Key = L.Location_key AND LN.Preferred= 1

2. Select S.Sample_Key, L.Location_Key, LN.Item_Name FROM Sample S LEFT JOIN Location L ON L.Location_Key = S.Location_Key
LEFT JOIN Location_Name LN ON LN.Location_Key = L.Location_key  WHERE  LN.Preferred= 1

3. Select S.Sample_Key, L.Location_Key, LN.Item_Name FROM Sample S LEFT JOIN Location L ON L.Location_Key = S.Location_Key
LEFT JOIN Location_Name LN ON LN.Location_Key = L.Location_key  WHERE  (LN.Preferred= 1  or  Ln.Preferred Is Null) 

4 Select S.Sample_Key, L.Location_Key, LN.Item_Name FROM Sample S LEFT JOIN Location L ON L.Location_Key = S.Location_Key
LEFT JOIN Location_Name LN ON LN.Location_Key = L.Location_key AND LN.Preferred= 1 

RIGHT JOINS

In the example below  the Location table is referred to before the Sample table so in order to 'preserve' the Sample data a RIGHT JOIN  is required. The LEFT JOIN between Location and Location_name remains unchanged because the Location table still referred to first.

Select S.Sample_Key, L.Location_Key, LN.Item_Name FROM Location L  RIGHT JOIN Sample S  ON L.Location_Key = S.Location_Key
LEFT JOIN Location_Name LN ON LN.Location_Key = L.Location_key AND LN.Preferred= 1