The hidden face of SELECT SQL Part 2

Level Extreme platform

Subscription

Corporate profile

Products & Services

Support

Legal

Français

Articles

Search:

The hidden face of SELECT SQL Part 2

Christian Desbourse, January 1, 2001

If you include two tables in a query without specifying a join condition, every record in the first table will be joined with every record in the second table, with huge results as a consequence.

Summary

If you include two tables in a query without specifying a join condition, every record in the first table will be joined with every record in the second table, with huge results as a consequence.

Description

The Cartesian join

If you include two tables in a query without specifying a join condition, every record in the first table will be joined with every record in the second table, with huge results as a consequence.

SELECT order_id, employee.emp_id FROM orders, employee
? _tally  && 16170
SELECT order_id, employee.emp_id FROM orders, employee  ;
               WHERE orders.emp_id=employee.emp_id
? _tally && 1078

The same happens with more than two tables and a join condition not covering all the tables involved in the query.

SELECT order_id, employee.emp_id FROM orders, employee, customer  ;
               WHERE orders.emp_id=employee.emp_id
? _tally  && 98098

Likewise, when joining tables containing empty fields, Foxpro will match the corresponding empty fields, producing once again enormous unexpected results. Let’s create a table Test1 with one field F1 (C, 1) and fill it with four records (A, B, C, D). Then copy the table to Test2

SELECT test1.f1 FROM test1,test2 WHERE test1.f1=test2.f1
? _tally  &&  4  (A, B, C, D)

After adding two blank records in Test1 and five blank records in Test2, the same query will give :

? _tally  && 14 (A, B, C, D and 10 blank records )

Using the EMPTY() or ISNULL() functions to complete the join condition will solve the problem.

SELECT test1.f1 FROM test1,test2 ;
       WHERE test1.f1=test2.f1 AND !EMPTY(test1.f1) AND !EMPTY(test2.f1)
? _tally  &&  4

One parent table, 2 child tables

We have an application designed to manage sales in a large company where access rights to the application have to be strictly defined. Each employee belongs to a group, and each group has access to a number of functions in the application. The customer wants to have a report listing, for each group, all the employees that belong to the group and all the functions to which the group has access. This document will be used to collect authorizations from department heads to the access rights.

We will use a simplified database made of 4 files : Employee, Group, Function and GrpFunc.

The report the customer wants looks like this, and was produced before in Word :

<B>Group : Sales</B>
						<B>Functions :</B>
						Customers
						Orders
<B>Members :</B>
Dupont, Sales Manager
Durant, Sales Coordinator
Demeyer, Salesman

<B>Authorizations :</B>

The first set of data needed to produce the report is a list of functions for each of the groups :

SELECT  ;
       grpfunc.grp_code, ;
       function.func_code, ;
       function.func_desc, ;
       groups.grp_desc, ;
       groups.grp_desc + function.func_desc AS order1 ;
       FROM function, grpfunc, groups ;
       WHERE function.func_code = grpfunc.func_code AND ; 
             grpfunc.grp_code=groups.grp_code AND ;
             grpfunc.grp_code IN (SELECT grp_code FROM group) ;
       ORDER BY grp_desc, func_desc

The second set of data is a list of employees that belong to each of the groups and can be obtained with a similar query.

SELECT ;
      groups.grp_code, ;
      employee.empl_name, ;
      employee.empl_func ;
      FROM employee, groups ;
      WHERE groups.grp_code = employee.grp_code ;
      ORDER BY grp_desc, employee.empl_name

Before we can join the two sets using the UNION clause, we must fill in the missing fields in each of the queries, by adding blanks using the AS clause. We will also add a field such as SubQuery which indicates from which part of the query the record originates. This will be useful for sorting out the output data and for producing the final report.

SELECT  ;
       ‘A’ AS SubQuery, ;
       grpfunc.grp_code, ;
       function.func_code, ;
       function.func_desc, ;
       groups.grp_desc, ;
       SPACE(20) AS empl_name, ;
       SPACE(20) AS empl_func ;
       FROM function, grpfunc, groups ;
       WHERE function.func_code = grpfunc.func_code AND ; 
             grpfunc.grp_code=groups.grp_code AND ;
             grpfunc.grp_code IN (SELECT grp_code FROM groups) ;
       ORDER BY grp_desc, func_desc       

SELECT ;
      ‘B’ AS SubQuery, ;
      groups.grp_code, ;
      SPACE(10) AS func_code, ;
      SPACE(20) AS func_desc, ;
      SPACE(20) AS grp_desc, ;
      SPACE(40) AS order1, ;
      employee.empl_name, ;
      employee.empl_func ;
      FROM employee, groups ;
      WHERE groups.grp_code = employee.grp_code ;
      ORDER BY grp_desc, empl_name

We still have to replace our two ORDER BY clauses by a single ORDER BY clause in the unioned query :

SELECT  ;
       ‘A’ AS SubQuery, ;
       grpfunc.grp_code, ;
       function.func_code, ;
       function.func_desc, ;
       groups.grp_desc, ;
       SPACE(20) AS empl_name, ;
       SPACE(20) AS empl_func ;
       FROM function, grpfunc, groups ;
       WHERE function.func_code = grpfunc.func_code AND ; 
             grpfunc.grp_code=groups.grp_code AND ;
             grpfunc.grp_code IN (SELECT grp_code FROM groups) ;
       UNION ;       
SELECT ;
      ‘A’ AS SubQuery, ;
      groups.grp_code, ;
      SPACE(10) AS func_code, ;
      SPACE(20) AS func_desc, ;
      groups.grp_desc, ;
      employee.empl_name, ;
      employee.empl_func ;
      FROM employee, groups ;
      WHERE groups.grp_code = employee.grp_code ;
      ORDER BY grp_desc, SubQuery, func_desc, empl_name

We have now the right data, in the right sequence needed to produce the report :

A few tricks will be needed to get the report exactly as the customer wants : Data grouping : 1. Grp_desc (New Page, Reprint Header on subsequent pages) 2. SubQuery (to separate the functions data from the employees data) Page header band : Document Title Group header (grp_desc) : Group description Group header (SubQuery) : column headers Column headers use the clause "Print when SubQuery=‘B’" for Employee columns and "Print when SubQuery=‘A’" for Function columns. Detail band : superposed Employee and Function fields

The self join

Using traditional xBase techniques, you could open a table in two diffrent work areas and establish a relation between the two, for instance to detect missing numbers in a sequence. As Rushmore is turned off when you link tables using SET RELATION, we will try a similar technique using SELECT SQL, called the self join. Let’s create a simple table, Table1 with one numeric field (Nr). The table contains a broken sequence of numbers: To prepare the self join, we will first open the table in two work areas, using two different alias :

USE table1 IN 0 ALIAS t1
USE table1 IN 0 AGAIN ALIAS t2

We want to list the numbers which are followed by a consecutive number in the file. The SELECT statement will use a join condition between t1 and t2, with a join condition specifying the link between consecutive numbers :

SELECT t1.nr, t2.nr FROM t1,t2 ;
               WHERE t2.nr=t1.nr+1

here is the result : we obtain in the Nr_a column only the numbers which have a consecutive number in the table .

Aggregate functions in WHERE, GROUP BY and HAVING clauses

Aggregate functions like SUM(), COUNT(), MAX(), cannot be used in WHERE and ORDER BY clauses, because they don’t apply to a single record. A workaround is using an alias name for the column names, or refer to the column by its number as in the following SELECT statement :

SELECT SUM(order_amt), to_country FROM orders GROUP BY to_country ORDER BY 1

If we want to limit the output to totals exceeding 100,000 the SELECT statement becomes :

SELECT SUM(order_amt), to_country FROM orders  ;
          GROUP BY to_country ;
          ORDER BY 1 ;
          HAVING SUM(order_amt) > 100000

As they apply to the whole set of data, aggregate functions can be used in the HAVING clause.

The same method, using the COUNT() function can be used to find duplicate values in a table : This statement gives the dates where more than one order was placed :

SELECT order_date, COUNT(*) FROM orders GROUP BY 1 HAVING COUNT(*)>1

Modify table structure with SQL SELECT

Table structures can be modified easily within an application using the SELECT SQL command : Let’s use our table1 table again, modifying the numeric field nr into a caracter field, and adding a name field of 20 caracters :

SELECT STR(nr,5,0), SPACE(20) AS Name FROM table1 INTO TABLE table2

Table 2 has the new structure we were looking for, and we just have to rename the table into table1 :

RENAME Table2.dbf TO Table1.dbf

String comparison differences

On the contrary to what happens in traditional xBase, SET EXACT ON/OFF has no effect on SQL SELECT. The equivalent here is obtained using SET ANSI ON/OFF. There are some differences though as we will see in the following example :

SET ANSI OFF
SELECT cust_id  FROM orders WHERE cust_id = ‘A’

results in all cust_id values beginning with A.

SET ANSI ON
SELECT cust_id  FROM orders WHERE cust_id = ‘A’

results in an empty cursor, as no cust_id value is exactly equal to ‘A’. Another difference with xBase is that the order of the operands can be changed without any difference in the results.

SELECT cust_id  FROM orders WHERE cust_id = ‘A’

is equivalent to :

SELECT cust_id  FROM orders WHERE ‘A’ = cust_id

while in xBase :

? "ANTON" = "A"   &&   .T.
? "A" = "ANTON"   &&   .F.

Christian Desbourse, Ir C Desbourse
Christian Desbourse is an independant consultant providing services in database development and Visual Foxpro programming. Independent developer of business applications. Business experience in logistic and industrial project management. Microsoft MVP from 1996 to 2002. MCP Visual Foxpro Desktop and distributed applications