John,
Ed's "O1" and "O2" have Oh's not zeroes. Starting with a digit would generate an error. Another way to express this is
FROM OrdItems AS O1
and it means that there is less to type elsewhere in the SELECT statement. Personally, I'm for long names as otherwise I am always looking back to see which table we mean, but if you are using different DBC's you could have
really long names.
You can have as many ANDs and ORs in your WHERE clause as you need. Use parens to force them to be evaluated correctly, or simply for ease in understanding what's going on.
>>>How do you find like all orders that included a hammer AND nails? This is assuming a table normalized like ORDITEMS in TESTDATA. That is one item per record. INLIST(orditems.item,"hammers","nails") works fine for an 'ANY' scenario but not an 'ALL'
>
>>
>>
SELECT * FROM Orders ;
>> INTO CURSOR Temp ;
>> WHERE OrdID IN (SELECT OrdID FROM OrdItems O1 WHERE item = 'hammer') ;
>> AND OrdID IN (SELECT OrdID From OrdItems O2 WHERE item = 'nails')
>>
>>I'd make sure that OrdItems had indexes on OrdID and Item.
>
>Cool Ed. Can you continute the AND however many times needed? And what is the 01 and 02? That won't generate an error?