Re: Help writing SQL query

From: Damjan S. Vujnovic <damjan_at_NOSPAMgaleb.etf.bg.ac.yu>
Date: Wed, 20 Nov 2002 11:40:01 -0800
Message-ID: <arfopu$p03$1@news.etf.bg.ac.yu>

"Tony Cantara" <tonycantara123_at_hotmail.com> wrote in message
> > > (initially I thought this would be an easy query to write using a
> > > CONNECT BY PRIOR construct clause but this doesn't work for
> > > 'cyclic/loop data' like that below).
> > > Many thanks
> > > Tony
>
> Mike, I think you have solved the approach. I couldn't fathom how to
> get around the 'loop' problem with the CONNECT BY PRIOR clause until
> seeing your code. All I have to do now is code the routines in a
> generic sense (another table with shape connectivities that may want
> to search for) so that I can, arbitrarly, count the number of
> triangles, rectangles, pentagons, shapes with triangles and rectangles
> in them, shapes with rectangles and only one triangles but no
> pentagons ...

Hm, discussable unless you change you model... When inserting touple (shapeID,node,nextnode_linkedlist) insert (shapeID,nextnode_linkedlist, node) as well. Consider the following shape:

1---------2

|         |  \
|         |     \
|         |        \
3---------4---------5
|         |         |
|         |         |
|         |         |

6---------7---------8

It's composed of three rectangles and one triangle, so it's representation using your model will look somewhat:

INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,1,2); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,2,4); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,4,3);
INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,3,1); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,3,6);
INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,6,7); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,7,4); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,4,5);
INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,5,8); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,8,7); INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES (1,5,2); Notice that this shape also has two pentagons (125431 and 742587) which your query won't find (because of the improper orietation). Although there is a lot of different possibilities for representing this shape it's impossible to represent it properly by always inserting only one of the touples (shapeID,node,nextnode_linkedlist)
(shapeID,nextnode_linkedlist, node)

You can give it a try, but either you'll be missing a triangle or a rectangle or a pentagon. Of course not all pairs (shapeID,node,nextnode_linkedlist) & (shapeID,nextnode_linkedlist, node) are needed in order to make a representation complete, but if you always insert both of them (a simple trigger will do it for you), you won't have any problems.

My 2 cents,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Wed Nov 20 2002 - 13:40:01 CST