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]
