How to add the filter condition to the JOIN clause in a SELECT-SQL command in Visual FoxPro (268906)

SUMMARY

When you are doing OUTER JOINs on tables, you might occasionally want to filter on the side of the table that would otherwise return NULLs. However, if you filter by using a WHERE or HAVING clause, you eliminate all the records on the parent side of the join that do not match the condition.

To get the desired results, you can add the filter condition to the JOIN clause.

MORE INFORMATION

As an example, you might want a list of all your customers, but want to pay special attention to the customers who have spent over $500 in freight. Typically, the first attempt at this looks similar to the following code, which you can paste into a .prg file or the Command window:

lcVersion = VERSION()
DO CASE
   CASE "5.00" $ lcVersion
      CD HOME() + "samples\data"
   CASE "6.00" $ lcVersion
      CD HOME(2) + "data"
ENDcase
SET NULLDISPLAY TO ""

SELECT customer.cust_id, order_id, freight ;
   FROM customer ;
      LEFT OUTER JOIN orders ON orders.cust_id = customer.cust_id ;
      WHERE orders.freight >= 500.00

The result shows only the customers who have the high freight amount, and omits all of those with lower amounts. There are ways to get around this by using UNIONed SELECTs with WHERE...NOT IN clauses, but by far the simplest solution is to replace the SELECT in the above code with the following:

SELECT customer.cust_id, order_id, freight ;
   FROM customer ;
      LEFT OUTER JOIN orders ON orders.cust_id = customer.cust_id ;
         AND orders.freight >= 500.00

REFERENCES

For additional information about the SELECT - SQL command, see the Visual FoxPro Help file.

For additional information about filtering OUTER JOINS, click the article number below to view the article in the Microsoft Knowledge Base:

268022 PRB: SELECT Returns Invalid Rows When Filtering Outer Join on Child Table