Re: Is this SQL Possible??

On Mon, 20 Dec 2004 12:11:27 -0700, Ben Sauer <SauerBL_at_ldschurch.org> wrote:
> I have a many to one relationship from table b to table a. In other words,
> in
> table A I have people and in table B I have assignments. I need to write a
> query to pick up a set of users in table A and just one of their assignments.
> I
> don't even care which one. Is there a way to do that elegantly?
>
> I thought this would work... but it's just too time consuming.

The only thing to comes to mind is that I assume that each assignment has some sort of unique ID so maybe you could use a subquery like:

select code, max(jobno) assignment
from ss
group by code;

 to get one and only one assignment for each person. If you're worried about performance then creating a materialized view based on that query and turning on all the query rewrite stuff should help. As you say that you don't worry about which assignment is pulled back it should matter if the materialized view refresh doesn't happen very often.

A materialized view for the:

 (SELECT "Support Staff ID", "Group ID"

         FROM gd WHERE "Full Name" IN (SELECT DISTINCT "Full Name"
                                         FROM gd))

inline view may help.

There maybe a more elegant and quicker solution but that's what comes to mind right now.

Stephen

--
http://www.freelists.org/webpage/oracle-l