Peter,

What you asking about is called a self join and yes it can be done. Here's an example;

SELECT Aa.Fld4, Aa.Fld5, Aa.Fld6 ;
  FROM MyTable Aa ;
  JOIN MyTabl Ab ;
    ON Aa.Fld1 = Ab.Fld1 ;
 WHERE Ab.Fld1 = "Blue" ;
 INTO CURSOR MyResult

Of course this example is not really that good because the following query gets the same results;

SELECT Fld4, Fld5, Fld6 ;
  FROM MyTable 
 WHERE Fld1 = "Blue" ;
 INTO CURSOR MyResult