Re: Help for advanced SQL query

"gene" <usenet_at_smalltime.com> wrote in message news:9a95c0dd.0105251735.48b6742c_at_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.

Here's a possible solution to q1. Haven't run it but it should be pretty much correct and should point you in the right direction.

1.

select o.*, m.*
from object o, map m
where m.cluster_id = o.cluster_id and

            m.rowid in (select m2.rowid
                                from map m2
                                where m2.cluster_id = o.cluster_id
                                group by m2.position)

2.

I can't think of a simple way to concatenate a column from an unknow number of records into a single record.

Not as a simple SQL anyway. Received on Sat May 26 2001 - 01:30:41 CDT