Re: Help writing SQL query

Damjan, I've been slightly simplistic in my original post.

There are also dodecahedrons in the table, and lots of them, and I expect lots of shapes in-between the triangle and these more complex shapes. That's a lot of self-joins for the type of solution you propose as a 'last resort' ! So what I'm looking for is an idea on how to approach the problem, or solutions using my existing table structure.
Thank you
Tony

"Damjan S. Vujnovic" <damjan_at_galeb.etf.bg.ac.yu> wrote in message news:<ar7sbg$i7d$1_at_news.etf.bg.ac.yu>...
> As a LAST resort, you could
>
> SELECT COUNT(*)
> FROM tblshapes s1, tblshapes s2, tblshapes s3
> WHERE s1.shapeID=s2.shapeID AND
> s2.shapeID=s3.shapeID AND
> s1.nextnode_linkedlist=s2.node AND
> s2.nextnode_linkedlist=s3.node AND
> s3.nextnode_linkedlist = s1.node
> GROUP BY s1.shapeID
>
> This is probably completely useless solution (I don't know the max # of
> touples in tblshapes). It works only if you represent your shapes in a
> smart waym i.e. you represent triangles sequentially, not like in the
> last example:
>
> (shapeID, node, nextnode_linkedlist)
> (300,20,23)
> (300,23,22)
> (300,22,20)
> (300,20,21)
> (300,21,22)
>
> or else the condition becomes much more complicated.
>
> 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/
> "Tony Cantara" <tonycantara123_at_hotmail.com> wrote:
> > Hi, I have designed a table that contains the following information
> > about some simple shapes
> >
> > I need a bit of help writing some SQL (Oracle 8i/9i Enterprise). I
> > would like to query the database and count, for example, the number of
> > shapes with triangles in them. For all the data below this would
> > return 2 (1 for the shape that is a triangle and 1 for the shape that
> > is a rectangle made up of 2 triangles).
> >
> > If the design is bad, I would appreciate constructive critism on what
> > would be a better design - at this stage nothing is set in concrete
> > (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
> >
> >
> > CREATE TABLE tblshapes(shapeID NUMBER,node NUMBER,nextnode_linkedlist
> > NUMBER);
> >
> > -- RECTANGLE
> > -- 1---------2
> > -- | |
> > -- | |
> > -- | |
> > -- 4---------3
> > --
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,1,4);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,2,1);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,3,2);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (100,4,3);
> >
> >
> > -- TRIANGLE
> > -- 10
> > -- |\
> > -- | \
> > -- | \
> > -- | \
> > -- 11---12
> > --
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (200,10,12);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (200,11,10);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (200,12,11);
> >
> >
> > -- TWO TRIANGLES ADDED TOGETHER TO MAKE A RECTANGLE
> > -- 20----21
> > -- |\ |
> > -- | \ |
> > -- | \ |
> > -- | \|
> > -- 23----22
> > --
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,20,23);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,20,22);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,21,20);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,22,21);
> > INSERT INTO tblshapes(shapeID,node,nextnode_linkedlist) VALUES
> > (300,23,22);
Received on Mon Nov 18 2002 - 08:51:18 CST