>>REMEMBER THIS:
>>you can transform the "not in" or "not exists" condition in an INNER JOIN,
>>into a LEFT JOIN with a WHERE IS NULL condition.
>>

>>select
>>...
>>from trans T INNER join Trans_Employees_Queues Q on T.cTrans_pk = Q.cTrans_fk
>>INNER JOIN ....
>>LEFT JOIN Employee_Queue_Schedules S ON S.FK=
>>WHERE .... and S.FK IS NULL
>>
>>

>________________________________
>Fabio,
>

Gregory,
the SQL sequence is:

- JOIN     : with JOIN rule and ON condition, produce the workset
- WHERE    : applies a condition to the workset
- GROUP BY : trasform the workset
- HAVING   : applies a condition to the workset
- DISTINCT : remove duplicate
- ORDER BY : sort the workset

the optimizer can apply some theorem of "Relational Algebra" to try to reduce the quantity
of data to be manipulate.
In the case of an left outer join the WHERE's LeftTable.conditions can be "anticipated",
but for the RightTable this "anticipation" is possible except when the condition is "IS NULL".

CLEAR 
SET OPTIMIZE ON 

create cursor TABLE1 (FIELD1 I)
INDEX ON FIELD1 TAG T1
	insert into TABLE1 values(1)
	insert into TABLE1 values(2)


	Create cursor TABLE2( FIELD2 I NULL, FIELD3 I,FIELD4 I )
INDEX ON FIELD2 TAG T2
INDEX ON FIELD3 TAG T3

	insert into TABLE2 values (1	,NULL,1)
	insert into TABLE2 values (1	,3,2)
	insert into TABLE2 values (1	,3,2)

CLEAR

SYS(3054,12)

* this uses T3 index
SELECT FIELD1,FIELD4 ;
into cursor tLeftWhere ;
	from TABLE1 LEFT JOIN TABLE2 ON FIELD1 = FIELD2 ;
	WHERE	FIELD3 is NOT null

* this doesn't use T3 index	
SELECT FIELD1,FIELD4 ;
into cursor tLeftWhere ;
	from TABLE1 LEFT JOIN TABLE2 ON FIELD1 = FIELD2 ;
	WHERE	FIELD3 is null

>Shouldn't that be HAVING instead of WHERE ?
>
No. They are equal on this context.

The use of HAVING in VFP is out standard.
On the standard SQL,
HAVING can be applied only if a clause GROUP BY exists.
This because without GROUP BY,
HAVING can serve only for to refer to the column names of the result;
obviously here the VFPT has gone to confusion and it uses HAVING with the same
rules of the WHERE, and this can bring to notable problems of syntax.
Unless HAVING it is never optimized.

>WHERE decides what records to include from the table(s), ie where defines the subset of each table that will participate in the select
>
Not true. It is a condition on the workset, not on table members.

>HAVING decides what records to keep in the final resultset (ie after the WHERE and OUTER JOIN have been applied)

See before.

>
>So, since no records have (fk is null), no records will pass the WHERE clause. Look at the output of (3)

Have you looked the (3) ? It return one row.

>
>As far as I can see, the sequence is WHERE - OUTER JOIN - HAVING
>

Not true.

>

>create cursor Customer (c_id I)
>	insert into Customer values(1)
>	insert into Customer values(2)
>	
>	Create cursor Orders( ord_id I, Ord_c_id I )
>	
>	insert into Orders values (22, 2)
>	insert into Orders values (24, 2)
>	
>	&& find customers without order(s)
>	
>	&& (1)
>	select	c_id ;
>		from Customer ;
>		into cursor tmp1 ;
>		where	( not exists ;
>					(select * from Orders where (Ord_c_id = c_id) ) ;
>				);
>				
>	brow
>	
>	&& (2) left join, with having
>	select	c_id, ;
>			ord_id ;
>		from Customer ;
>			left join Orders on (Ord_c_id = c_id) ;
>		into cursor tmp2 ;
>		having ( ord_id is null )
>	
>	brow
>	
>	
>	&& (3) left join, with where
>	select	c_id, ;
>			ord_id ;
>		from Customer ;
>			left join Orders on (Ord_c_id = c_id) ;
>		into cursor tmp3 ;
>		where ( ord_id is null )
>	
>	brow
>

Fabio