Re: Is this SQL Possible??

  • From: stephen booth <>
  • To: SauerBL@xxxxxxxxxxxxx
  • Date: Mon, 20 Dec 2004 20:00:52 +0000

On Mon, 20 Dec 2004 12:11:27 -0700, Ben Sauer <SauerBL@xxxxxxxxxxxxx> 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

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.


Other related posts: