Re: Help writing SQL query

From: Damjan S. Vujnovic <damjan_at_galeb.etf.bg.ac.yu>
Date: Sun, 17 Nov 2002 11:52:17 -0800
Message-ID: <ar7sbg$i7d$1@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 Sun Nov 17 2002 - 13:52:17 CST