All I was demonstrating was how to use a single field. It's obviously better to use a second table and build a relationship using JOIN.
I don't see where "multi-occurrence" fields can do something that JOIN-ed relationships can't. SELECT LastName FROM Contacts JOIN Phones USING (phoneID) HAVING PhoneNumber='2064814442'; or perhaps: SELECT LastName FROM Contacts WHERE phoneID IN (SELECT phoneID FROM Phones WHERE PhoneNumber='2064814442'; Thus it would seem that this can simply be implemented in terms of syntax (automatic table creation, rewriting queries for implicit joins, etc). You'll note I never said that this feature is "uncommon" - merely unnecessary, kludgy, and queries written in this manner are difficult to read, and as at least you've noticed, difficult to port. It would be nice for the engine to do it automatically for you. But it would make the engine more complex- for a direction that isn't standard, that doesn't add the ability to answer questions that previously could not, nor the ability to answer questions faster than before. That is, as near as I can tell, it only provides the ability to answer questions with less typing. Even if BASIS is slower for these implicit joins, that doesn't mean other systems are (PostgreSQL isn't, for example). What it does mean is that BASIS has a crappy query optimizer. That said, I really would like to see user-defined "structures"- but I suspect this will have to wait for SQLite 3.0 or whenever we get non-null-terminating values... On Mon, 2003-12-15 at 07:25, Steve O'Hara wrote: > Here's my tuppence worth; > > I've been working with BASIS for donkeys years and it is one of the few > RDBMS that fully supports multi-occurence fields. > To search for a value in such a field you simply say > SELECT LastName FROM Contacts > WHERE Phones = "2064814442" > This means search in any occurance within Phones > > If you want a specific entry then you say > SELECT LastName FROM Contacts > WHERE Phones(3) = "2064814442" > > Interestingly, if you have a number of fields that have matched values e.g. > Phone, Extension etc you can do this > SELECT LastName FROM Contacts > WHERE Phones="2064814442" WITH Extension="234" > This means find records where the same occurance of Phones and Extension > meets the criteria. > > Although BASIS is fully relational, this feature is very commonly used > instead of creating linked lookup tables and doing a join, because of the > poorer performance of joins. The techniques shown here by Mrs Brisby are > fine but because they use LIKE, they don't use an index and so will be very > slow for large databases. > > However, there are some disadvantages to this stuff; > > Multi-occurance fields are not common to RDBMS > > Sorting can be interesting (you might have to do an "exploding" sort) e.g. > SELECT LastName FROM Contacts > WHERE Phones = "2064814442" ORDER BY EVERY Phones > or > SELECT LastName FROM Contacts > WHERE Phones = "2064814442" ORDER BY Phones(1) > > More complexity in SQLite > > Steve > > > > > -----Original Message----- > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: 12 December 2003 13:56 > To: Yogesh Vachhani > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Let us make SQLite more powerful > > > On Fri, 2003-12-12 at 08:09, Yogesh Vachhani wrote: > > For example, if the Phones column is a collection, one can find all > > rows that contain the phone number 206-481-4442 by specifying the > > following SELECT statement: > > > > SELECT LastName > > FROM Contacts > > WHERE Phones(ANY) = "2064814442" > > Why not encode Phones as ":phonenumber:" > that way you can already do: > SELECT LastName FROM Contacts WHERE Phones LIKE '%:phonenumber:%' > > (btw, your quoting is wrong) > > > 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:%'; > > > > For example, lets return to the contact database, which contains a > > Phones column whose > > data type is PhoneStructure, so the Phones column contains the > > columns PhoneType and PhoneNum. The following query could returns > > just the phone number 206-402-9400 and not the phone type for that > > number: > > > > SELECT Phones(*)!PhoneNum > > FROM Contacts > > WHERE LastName = "Calder" > > This didn't require any special syntax. > > > The following query would return the phone numbers for every contact > > who has a fax number: > > > > SELECT Phones(*)!PhoneNum > > FROM Contacts > > WHERE Phones(ANY).PhoneType = "fax" > > Instead of encoding phone numbers as :phonenumber:, you could also use > :type=phonenumber: > > > Suppose that we put the PhoneType and PhoneNum in a separate table > > instead of in a PhoneStructure. To tie rows in the Phones table to a > > contact in the Contacts table, we then add a RefTo collection column > > called PhoneRef to the Contacts table. This RefTo column contains the > > Row IDs of one or more rows in the Phones table. To access phone > > numbers in the Phones table, one uses an identical query (except for > > minor name changes for clarity): > > > > SELECT PhoneRef(*)!PhoneNum > > FROM Contacts > > WHERE PhoneRef(ANY).PhoneType = "fax" > > This is redundant. > > > > 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. > > > > So one can see that one would not lose anything by using SQLite's > > extended storage model (if implemented). Even though the LineItems > > information is embedded into the Invoices table, one can still write > > a simple query to access it as if it were in an independent table. > > Backwards compatibility gets lost. > API changes so programs need to be altered. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
