Re: [SQL] set type and in clause

At 0:25 +0200 on 20/1/99, frank wrote:

>
> i suppose it´s been asked before (i´ve seen the question, but not the
> answer, in the mailing list archives):
>
> how do you create a set type, i.e. an attribute which is, say ´set of
> char´; and then you´d want to run some query like
>
> select . . . from . . . where xxx in yyy
>
> is such a data type supported by postgresql? do other dbms´s support it?

According to the relational model, a set is a separate table which is
connected to your main table by a foreign key. That is, you hold two tables:

Main table:

P.Key 1:1 Data
----- --------
10 Microsoft
20 Adobe
30 FSF
...

Secondary table:

F.Key 1:N Data
----- --------
10 Office
10 Access
20 Photoshop
30 gcc
10 Windows
20 Acrobat
30 bison
30 flex

In this manner, you have the strings 'Office', 'Access' and 'Windows'
associated with the record for 'Microsoft', the strings 'Photoshop' and
'Acrobat' associated with the record for 'Adobe', and the strings 'gcc',
'bison' and 'flex' associated with the record for 'FSF'.

Suppose the names of the fields are 'p' for the primary key, 'company' form
the 1:1 data, 'f' for the foreign key and 'product' for the 1:N data, you
can write a query like this:

SELECT company, product
FROM main, secondary
WHERE p=f.

The result would be:

Microsoft Office
Microsoft Access
Microsoft Windows
Adobe Photoshop
Adobe Acrobat
FSF gcc
FSF bison
FSF flex

The point I am trying to get across is that this, in fact, represents a set
associated with each record. You get the data you wanted. The problem is
that in the result you get repetitions, because that's how the relational
model works. If you want to isolate the set, you can use a frontend which
drops the repetitions and creates the following report from them:

Microsoft Office
Access
Windows
Adobe Photoshop
Acrobat
FSF gcc
bison
flex

Nevertheless, the data represented by this report is the same data that you
got from the original query. It merely shows association with the company.
So, if you wanted to test whether something belongs or does not belong to a
set that is associated with one of your software vendors, you can do a
subquery on the table. For example, to get the companies which have in
their set of products a product called 'Acrobat', you write:

SELECT company
FROM main
WHERE 'Acrobat' IN (
SELECT product
FROM secondary
WHERE p=f );

I'd like to note that having the set of related products included in the
main table is, well, not relational.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma