Re: table join problem

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1073668017.378941_at_yasure>...
> 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
>
> Since this is school work ... post your best attempt to solve it and we
> will give you hints.

No this is not a school work, real problem is much more complicated. I have 3 connected tables (Ticket->TicketRowOdds->MatchOdds) and each table has more than 100000 rows. I need the fastest possible solution, everything I've already tried is very slow. My fastest solution is:

SELECT 'everything i need'
FROM Ticket t, TicketRowOdds tro, MatchOdds mo

WHERE (tro.ticketId = t.id) AND 
      (mo.matchId = tro.matchid) AND 
      (exists (select * 
               from TicketRowOdds tro2, MatchOdd mo2 
               where (tro2.ticketId = t.id) AND 
                     (mo2.matchid = tro2.MATCHID) AND 
                     (mo2.something > t.something)))

Because of "(mo2.something > t.something)" I need to perform this join.
I use Oracle 8.1

Thanks in advance,
Perica Milosevic Received on Mon Jan 12 2004 - 02:59:30 CST