From: "Yanick Hudon" Newsgroups: comp.databases.oracle.misc Subject: Complex Query Lines: 46 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Message-ID: Date: Sun, 7 Jul 2002 10:35:00 -0700 NNTP-Posting-Host: 65.92.205.37 X-Complaints-To: abuse@sympatico.ca X-Trace: news20.bellglobal.com 1026053420 65.92.205.37 (Sun, 07 Jul 2002 10:50:20 EDT) NNTP-Posting-Date: Sun, 07 Jul 2002 10:50:20 EDT Organization: Bell Sympatico Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!nycmny1-snh1.gtei.net!news.gtei.net!bloom-beacon.mit.edu!newsswitch.lcs.mit.edu!snoopy.risq.qc.ca!torn!webster!nf1.bellglobal.com!nf2.bellglobal.com!news20.bellglobal.com.POSTED!not-for-mail Xref: easynews comp.databases.oracle.misc:83904 X-Received-Date: Sun, 07 Jul 2002 07:56:03 MST (news.easynews.com) Hi to all I need some helps to perform a query based on cross-table links. - I have 9 levels of average whose are different name in a table; - I have also all the people whose can subscribe to an event; - I have another table which determine members based if a level is present or not; - ! have a table which defines the score for all the participants to the eventID; Now here's the query i need: I need a ranking query based on the levels of the members order by Score Ascending with some particularities: --> The first two levels have to be together for the 10 first position, after that i take only the first level; If i have a member in those 2 levels which is 50 years old and more, i shall include him; --> The same rule applies to levels 7 and 8; --> All the others levels, If i have a member in those which is 50 years old and more, i shall include him; --> And finally for all the others levels, they're stand alone. My first attempt to the query perform what i want except that after the 10 first position in level 1 and 7, the members whose are in the level after are still the level 1 and 7, because i'm treating them as level 1 and 7 in another table that i create where it's saying that a member can participate in 2 or more level. I Change the levels after inserting the record with a Union Query between level 1 and 2, also i did the same thing with level 7 and 8. Is there a way to perform what i want without the last table i created?? Any helps would be appreciated Thanks in advance.