On Fri, 2003-12-12 at 09:58, Paul Smith wrote: > > > 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..
oh shit. so it would. > (Regexps? ;-) ) That's an idea.... anyway, it can already be done with SQLite by supplying your own function. Additionally, you could add lists like this yourself (pick a terminator, and make a function that indexes) > > > 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. That's what I think everyone is used to... But I keep seeing this come up. I see it in other places. I'm not saying there aren't some really "cool" nonstandard features, but this particular system seems like it's fancy syntax, and doesn't really do anything you couldn't do before... --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
