>Good Morning,
>
>I have a table named modacad which lists a number of records that have a type associated with them. The four types are SAMPLE, TECHNICAL, COST EXERCISE or MOCK-UP, the type field is known as modacad.key4. I would like to produce a list of records of all samples and also any matching technical records. Therefore I have created 2 SQL statements:
>
>STATEMENT1
>
>SELECT modacad.key1, modacad.key2, modacad.key3, modacad.key4, modacad.mainkey ;
>FROM "U:\Data\modacad.dbf" ORDER BY modacad.key1 ;
>WHERE modacad.key4='SAMPLE' ;
>INTO CURSOR result1
>
>STATEMENT 2
>
>SELECT modacad.key1, modacad.key2, modacad.key3, modacad.key4, modacad.mainkey ;
>FROM "U:\Data\modacad.dbf" ORDER BY modacad.key1 ;
>WHERE modacad.key4='TECHNICAL' ;
>INTO CURSOR result2
>
>This will give me in one cursor all samples and in the other cursor all technicals.
>
>What I would like to do now is to join the 2 tables together so that it will list all Samples and ONLY those records that are Technical. Both the samples records and the equivalent technical records have the same ID. For example
>
>This is what I have
>
>JKT001 Sample
>JKT001 Technical
>JKT001 Mock-up
>JKT002 Sample
>JKT003 Sample
>JKT003 Technical
>JKT004 Sample
>JKT004 Mockup
>
>When I run the query it needs to list all samples and those matching Technicals. Therefore my results should say.
>
>JKT001 Sample
>JKT002 Sample
>JKT003 Sample
>JKT001 Technical
>JKT003 Technical
>JKT004 Sample
>
>therefore I want all sample records + any matching technical records.

Select distinct t1.* ;
  from ("U:\Data\modacad.dbf") t1 ;
  inner join ("U:\Data\modacad.dbf") t2 ;
      on t1.key1 = t2.key1 and t1.key4#t2.key4 ;
  where t1.key4 in ('Sample', 'Technical') ;
  order by t1.key1

PS: Assumed JKT000x was key1.
I missed you want all samples. Make the join 'left join'

Rereading sounds what you want is :

Select distinct t1.* ;
  from ("U:\Data\modacad.dbf") t1 ;
  inner join ("U:\Data\modacad.dbf") t2 ;
      on t1.key1 = t2.key1 ;
         and (t1.key4#t2.key4 or t2.Key4 = 'Sample') ;
  where t1.key4 in ('Sample', 'Technical') ;
  order by t1.key1

Cetin