Re: Help writing SQL query

Ok. Definitely a bad idea... I think you should reconsider your model. What do you think about:

CREATE TABLE point (

    point_id INT NOT NULL PRIMARY KEY);

CREATE TABLE shape (

    shape_id INT NOT NULL PRIMARY KEY,
    comment VARCHAR(50) NULL);

CREATE TABLE triangle (

    shape_id INT NOT NULL PRIMARY KEY REFERENCES shape(shape_id),

    point1_id INT NOT NULL REFERENCES point(point_id),
    point2_id INT NOT NULL REFERENCES point(point_id),
    point3_id INT NOT NULL REFERENCES point(point_id));

CREATE TABLE rectangle (

    shape_id INT NOT NULL PRIMARY KEY REFERENCES shape(shape_id),

    point1_id INT NOT NULL REFERENCES point(point_id),
    point2_id INT NOT NULL REFERENCES point(point_id),
    point3_id INT NOT NULL REFERENCES point(point_id),
    point4_id INT NOT NULL REFERENCES point(point_id));

CREATE TABLE
whatever_you_need_of_so_called_simple_shapes_you_want_to_query_for (

    shape_id INT NOT NULL PRIMARY KEY REFERENCES shape(shape_id),     ...);

CREATE TABLE complex_shape (

    shape_id INT NOT NULL PRIMARY KEY REFERENCES shape(shape_id));

CREATE TABLE contains (

    parent_shape_id INT NOT NULL REFERENCES complex_shape(shape_id),     child_shape_id INT NOT NULL PRIMARY KEY REFERENCES shape(shape_id));

Note that not every two triangles added together make a rectangle (they don't have to be planar). So, in order to represent the same system as in your first post, you'll have to

INSERT INTO point(point_id) VALUES (1);
INSERT INTO point(point_id) VALUES (2);
INSERT INTO point(point_id) VALUES (3);
INSERT INTO point(point_id) VALUES (4);
INSERT INTO point(point_id) VALUES (10);
INSERT INTO point(point_id) VALUES (11);
INSERT INTO point(point_id) VALUES (12);
INSERT INTO point(point_id) VALUES (20);
INSERT INTO point(point_id) VALUES (21);
INSERT INTO point(point_id) VALUES (22);
INSERT INTO point(point_id) VALUES (23);

INSERT INTO shape(shape_id, comment) VALUES (1, 'the rectangle'); INSERT INTO rectangle(shape_id, point1, point2, point3, point4) VALUES (1, 1, 2, 3, 4);

INSERT INTO shape(shape_id, comment) VALUES (2, 'the triangle'); INSERT INTO triangle(shape_id, point1, point2, point3) VALUES (2, 10, 11, 12);

INSERT INTO shape(shape_id, comment) VALUES (3, 'first triangle part of the rectangle');
INSERT INTO triangle(shape_id, point1, point2, point3) VALUES (3, 20, 21, 22);
INSERT INTO shape(shape_id, comment) VALUES (4, 'second triangle part of the rectangle');
INSERT INTO triangle(shape_id, point1, point2, point3) VALUES (4, 20, 22, 23);
INSERT INTO shape(shape_id, comment) VALUES (5, 'rectangle composed of two triangles');
INSERT INTO complex_shape(shape_id) VALUES (5); INSERT INTO contains (5, 3);
INSERT INTO contains (5, 5);

The query is:

SELECT count(distinct has.parent_id)
FROM contains has, shape child
WHERE has.child_shape_id = child.shape_id AND

      child.shape_id IN (
          SELECT shape_id
          FROM triangle)

Note that in a given model a shape cannot be both a "rectangle" and a "rectangle composed of two triangles".

--
Regards.
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:


> 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