>>Jim --
>>
>>Won't 2 conditions in the WHERE clause suffice, rather than using a subquery in this situation?
>
>If the values are in two different fields, I assumed by the discussion that the data was in a single field and the searching was using a $ match. In that case the subselect is the only way to get what he wanted.

Jim --

I had a chance to develop a little test program which confirmed for me that both approaches return the same set of values.

I'm trying to understand better when subqueries are used. After all, a subquery is just another filter condition. So, when is it required?

1. In the same table when doing a self join (which indicates a hierarchical relationship (or at least an interrecord relationship) between data in that table -- such as supervisor/supervisee).

2. The subquery provides FK values derived from another table. A correlated subquery provides additional join conditions between the main and sub queries, but otherwise works the same way as a plain subquery in that the filter is FK values from another table.

I wonder if there are there other scenarios?

Jay

SET DEFAULT TO D:\test\SQLWhere
lcPath = SET ("PATH")
SET PATH TO

IF NOT FILE ("SQLW.DBF")
	CREATE TABLE SQLW (nKey n(1), cStat c(2))
	SELECT SQLW
	INSERT INTO SQLW (nKey, cStat) VALUES (1, "A")
	INSERT INTO SQLW (nKey, cStat) VALUES (2, "AB")
	INSERT INTO SQLW (nKey, cStat) VALUES (3, "AB")
	INSERT INTO SQLW (nKey, cStat) VALUES (4, "A")	
	INSERT INTO SQLW (nKey, cStat) VALUES (5, "")	
	INSERT INTO SQLW (nKey, cStat) VALUES (6, "D")	
ELSE
	IF NOT USED ("SQLW")
		USE SQLW IN 0
	ENDIF
ENDIF
SELECT SQLW

*	Query 1

*!*	SELECT * from SQLW WHERE "A" $ cStat AND NOT "B" $ cStat

*	Query 2
SELECT * ;
	FROM SQLW ;
	WHERE "A" $ cStat ;
		AND nKey IN (SELECT nKey FROM SQLW WHERE NOT "B" $ cStat)

IF NOT EMPTY (lcPath)
	SET PATH TO &lcPath
ENDIF

Jay