Hello Sergey,

you understood correctly. i had to make some minor modification to your querry. the following is a test setup of what i came up with and it gives me perfect results:

close data all

sele 0
use comast alias Report_ComastTable
sele 0
use codet alias Report_CodetTable

WhereStr2 = ;
   "Inlist(q_b.opt3, '03  ')"
FromStr = ;
   "Report_ComastTable q_a " + ;
   "LEFT JOIN Report_CodetTable q_b ON q_a.oeno = q_b.oeno"

SELE DISTINCT ;
      q_a.*, ;
      q_b.opt3, ;
      COUNT(*) AS TotCount, ;
      SUM( IIF(&WhereStr2, 1, 0) ) AS MatchCount ;
   FROM &FromStr ;
   HAVING MatchCount = TotCount ;
   GROUP BY q_a.oeno

as you can see i had too add the "group by" clause, but other then that it works great. this is a pre-querry to the main querry and allows me to pre proccess a header table based on detail.

Thanks again for your help Sergey!




>I'm not sure I understand what you want so I maybe answering wrong question.

* Get the list of Orders where all items match criteria
>SELECT OrderNumber, COUNT(*) AS TotCount, ;
>    SUM( IIF(< condition >, 1, 0) ) AS MatchCount
>  From details ;
>  HAVING MatchCount = TotCount
>

>
>
>>Oops, the subject should read:
>>If some detail does not match, throw the header out
>>
>>
>>Hello everybody.
>>
>>i've got 2 tables: sales order header (comast) and sales order detail (codet). i would like to display only sales orders where ALL of the details match certain criteria.
>>
>>i already have 2 scenarios completed:
>>1. show sales orders which contain some/all details matching the criteria (show all detail lines)
>>2. show sales orders which contain some/all details matching the criteria (show only matching detail lines)
>>
>>but the 3rd one i can not figure out the sql command. for the 1st scenario i prequerry the detail table by simply returning a new header with the orders containing anywhere the criteria.
>>
>>how do i do this?
>>
>>thank you for all your help.