Help for advanced SQL query

From: gene <usenet_at_smalltime.com>
Date: 25 May 2001 18:35:46 -0700
Message-ID: <9a95c0dd.0105251735.48b6742c@posting.google.com>

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. Received on Fri May 25 2001 - 20:35:46 CDT