> If one always store fax numbers in the third element of the Phones > collection, one could find all rows that contain a particular fax > number > like this: > > SELECT LastName FROM Contacts WHERE Phones(3) = "2064814442"
SELECT LastName FROM Contacts WHERE Phones LIKE ':%::%::phonenumber:%';
No, that would find phone number in the third, fourth, fifth etc element as well..
(Regexps? ;-) )
> One can query data in a collection or structure-valued column just as > one can query data in a conventional table. To the query interface, a > collection of structures should appears as a logical table with its > parent table name automatically prefixed. So, for instance, a query > to find all LineItems in the Invoices table would look like this: > > SELECT PartNo, QtyOrdered, UnitPrice, QtyOnHand, EditDate > FROM Invoices.LineItems
Why exactly do you think the JOIN operator is the wrong tool for this? If you think this is easier to read, then consider creating VIEWs to store intermediate queries.
That was my thought. SQL doesn't have collections (AFAIAA), and SQ **Lite** definitely shouldn't
You can do this by having a 'phone numbers' table with a contact ID column and join it to your contacts table linking on the contact ID. That's what we do with our systems here, and it works fine.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
