ODBC4x

Example SQL Using the ODBC Driver

	The examples below use two tables, Customer and SalesRep. The Customer table contains four fields CustomerId, Name, SalesRepId and ARBalance. The SalesReptable contains two fields SalesRepId and Name. The Customertable contains two rows
		CustomerId			Name		SalesRepId	ARBalance
		0001			ABC Corp		01	1234.99
		0002			Acme Inc			1.23
	The SalesReptable contains three rows
		SalesRepId			Name
		01			John Doe
		02			Jane Smith
		03			House Account
	The following tables illustrate the results of the three different joins.
Cross Join	SELECT Customer.Name, SalesRep.Name
			FROM Customer Customer, SalesRep SalesRep
	Result set ...
		ABC Corp				John Doe
		ABC Corp				Jane Smith
		ABC Corp				House Account
		Acme Inc				John Doe
		Acme Inc				Jane Smith
		Acme Inc				House Account
Inner Join	SELECT Customer.Name, SalesRep.Name FROM { OJ Customer Customer INNER JOIN SalesRep SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId } Result set ...
		ABC Corp				John Doe
Left Outer Join	SELECT Customer.Name, SalesRep.Name FROM { OJ Customer Customer LEFT OUTER JOIN SalesRep SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId } Result set ...
		ABC Corp				John Doe
		Acme Inc
Constructing a Left Outer Join Using the Syntax Table	The following example applies the syntax descriptors explained in the SQL Syntax Table. In the descriptions below, a "::=" symbol represents the phrase "consists of" and a "|" symbol represents an exclusive OR. Suppose we want a statement that will retrieve all rows from the Customer table, we want to display the customer’ name, and we also want to display the name of the sales representative when the customer has a sales representative assigned. Because the Customer table and the SalesReptable both contain a column called Name we must use an alias so that the ODBC driver can determine which column we are referring to. We begin with a statement ::= SELECT select orderby which consists of a select ::= selectcols FROMtablelist where groupby having, which has selectcols ::= selectallcols * | selectallcols selectlist. We are want to display a limited number of columns so we want a selectlist ::= selectlistitem , selectlist | selectlistitem, which consists of two selectlistitem ::= expression | expression aliasname | expression AS aliasname | aliasname.* | colref. Our two selectlist items are colref ::= aliasname . columnname | columnname which are composed of an aliasname ::= identifier and a columnname ::= identifier. An identifier consists of an identifier (identifiers containing spaces must be enclosed in double quotes). As the alias we have chosen to use the name of the table; however, an alias is not limited to the table name. Thus far we have: SELECT Customer.Name, SalesRep.Name FROM Now we parse the tablelist. A tablelist ::= tablelistitem , tablelist | tablelistitem where a tablelistitem ::= tableref | outerjoin and we want all rows from the first table and matching rows from the second table, if any. Therefore we need an outerjoin ::= ojescape | ojshorthand. Since we don’t like to type we use ojshorthand ::= {OJoj } where oj := tableref LEFT OUTER JOINtableref ON boolean. We have two tableref ::= tablename | tablename aliasname where tablename ::= identifier. Thus far we have: SELECT Customer.Name, SalesRep.Name
				FROM {OJ Customer Customer LEFT OUTER JOIN SalesRep SalesRep ON }
	The final piece is the relationship between the Customer table and the SalesRep table which is specified with boolean ::= and | and ORboolean. Ultimately we want a comparison which is part of not ::= comparison | NOTcomparison which is part of and ::= not | not ANDand. The comparison is expression op expression where the expressions are colref and the op is an =. The result is: SELECT Customer.Name, SalesRep.Name
				FROM {OJ Customer Customer LEFT OUTER JOIN SalesRep SalesRep ON Customer.SalesRepId = SalesRep.SalesRepId }