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
> >
