Re: Help for advanced SQL query

gene wrote:

> I want to do a SQL query which is a little too advanced for my skills.
>
> For this query there are two tables:
> table OBJECT
> ------
> object_id
> cluster_id
> name
>
> table MAP
> -----
> map_id
> cluster_id
> position
> source
>
> cluster_id is a foreign key in both tables that I am using to join
> OBJECT and MAP together. Most OBJECTS have one MAP, but this can be a
> many to many relationship. Currently, I am doing this query to get my
> mapping information:
> select
> OBJECT.name,
> MAP.position,
> MAP.source
> from
> OBJECT,
> MAP
> where
> OBJECT.cluster_id = MAP.cluster_id;
>
> What I would like to do is to modify the query so that, in the cases
> where there are multiple MAPs for one OBJECT, only one MAP per
> position is returned. In other words, if there are three MAPs for a
> given OBJECT with positions of 1.0, 2.0, and 2.0, then the 1.0 MAP and
> either one, but not both, of the 2.0 MAPs are returned.
>
> Another option, is to return only one MAP per OBJECT, with the
> position being the average of all the MAP positions for that object.
> The more tricky part with that is that the other MAP fields, in this
> example MAP.source, need to be combined as well, by concatenation of
> the text entries.
>
> Is this too much to try to do in SQL?
>
> Thanks.

It is far to late at night for me to think clearly and help you but I do want to comment on the fact that you have a Many-to-Many relationship between your tables: This is very bad design. You need to create a third table, an intersecting entity, with the primary keys from the other two.

Hopefully someone wider awake will help you with your immediate query.

Daniel A. Morgan Received on Sat May 26 2001 - 01:34:44 CDT