Selecting certain records based on priority criteria

[douggy]

I have two tables:

-Organisation
-Contacts

One organisation can have MNAY contacts.

I want to write a query that will take the company and return only ONE contact based on a given priority.

Eg: take the MD if there is one, if not take the fd, if not take the MK(Marketing Manager).

Can anyone help with this.

Kind regards

Mark

 

[WildWill]

Create another table that holds your priorities with a ranking:

EG

Title Rank
MD 1
FD 2
MK 3
……

Next use a nested query, such as:

SELECT Contacts.name,
       Contacts.jobTitle, 
       Contacts.thisandthat, 
       Contacts.Company, 
       Ranking.Rank
FROM   Ranking INNER JOIN Contacts ON Ranking.Title = Contacts.jobTitle
where  Contacts.Company="B"
and    Ranking.Rank = (
              select max(ranking.rank)
              FROM Ranking INNER JOIN Contacts ON 
                 Ranking.Title = Contacts.jobTitle
              where Contacts.Company = "B"
                       );

Hope This Helps

X-)
Billy H

bhogar@acxiom.co.uk