Microsoft KB Archive/268906

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

Article ID: 268906

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q268906



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 &quot;5.00&quot; $ lcVersion CD HOME + &quot;samples\data&quot; CASE &quot;6.00&quot; $ lcVersion CD HOME(2) + &quot;data&quot; ENDcase SET NULLDISPLAY TO &quot;&quot;

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

