Re: table join problem

Hans Forbrich wrote:
>
> Perica Milosevic wrote:
> >
> > Hello!
> >
> > I have master-detail relationship between two tables, for example:
> > Company (companyid, companyname), Worker(companyid, workerid,
> > workername, workerage) where the 'companyid' field is connection
> > between this two tables.
> >
> > I want to select ALL workers from all companies that employ workers
> > older than 60 (workerage > 60).
> >
> > For example:
> > COMPANY
> > companyid 1, companyname ORACLE
> > companyid 2, companyname SUN
> > companyid 3, companyname IBM
> >
> > WORKER
> > companyid 1, workerid 1, workername 'John', workerage 32
> > companyid 1, workerid 2, workername 'Smith', workerage 61
> > companyid 2, workerid 1, workername 'Peter', workerage 27
> > companyid 2, workerid 2, workername 'Jenet', workerage 33
> > companyid 2, workerid 3, workername 'Michael', workerage 38
> > companyid 3, workerid 1, workername 'Sarah', workerage 41
> > companyid 3, workerid 2, workername 'Nick', workerage 63
> >
> > I would like to get:
> > 1, 1, 'John', 32
> > 1, 2, 'Smith', 61
> > 3, 1, 'Sarah', 41
> > 3, 2, 'Nick', 63
> >
> > Thanks in advance,
> > Perica Milosevic
>
> Please share with us what you have attempted so far, so we can provide
> specific comments. Operating System and Database version would be nice,
> as there are minor differences that may be relevant.
>
> The solution is to join (using one of the join techniques), then project
> the right columns. Your example should not require any aggregates.

(Sorry - got diverted), should read

The solution is to use a join (using one of the join techniques), a multirow subquery identifying the minimum list of companies that meet the criteria, and a projection of the right columns.

HTH
/Hans Received on Fri Jan 09 2004 - 10:50:57 CST