Another option could be (if interested mainly in Comms and one particular customer):

WITH INSTEMP(INCIDENTID) AS
(SELECT I.ID FROM INCIDENTS I WHERE I.CUSTOMER_NAME = :NAME)

SELECT C.<Whatever>
FROM COMMS C
JOIN INSTEMP IT ON C.INCIDENTID = IT.INCIDENTID
WHERE NOT EXISTS(
SELECT NULL FROM COMMS C2
JOIN INSTEMP IT2 ON C2.INCIDENTID = IT2.INCIDENTID
WHERE C.xDATE < C2.xDATE)

Now, I'm still on Firebird 1.5 and WITH is only available in Firebird 2.1 and above (I think). If the optimizer choose a plan similar to:

PLAN JOIN(IT2 I INDEX(IDX_CUSTOMERNAME), C2 INDEX(INCIDENTID))
PLAN JOIN(IT I INDEX(IDX_CUSTOMERNAME), C INDEX(INCIDENTID))

then this could be reasonably fast. Unfortunately, I have no idea whether the optimizer in Fb 2.1 or 2.5 choose something like this or not. It would be nice if you tried and reported back.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 4. november 2008 09:17
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

At 06:53 PM 4/11/2008, you wrote:

>Hi Set,
>
>>I don't quite grasp your structure, so it is difficult to give sound
>advice. How is COMMUNICATIONS and INCIDENT defined, what is their
>>relationship and approximately how many records are there within each
>table and for each customer?
>Just answered in reply to Helen's mail, but in short:
>
>Table Records
>Customers 15000 PK ID
>Incidents 150000 PK ID, CustomerID references Customers.ID
>Comms 1500000 PK ID, IncidentID references Incidents.ID
>
>Need to extract max date in Comms per Customer

So what you said before isn't quite correct. You have a 1:1 relationship between an Incident and a Customer, and a 1:1 relationship between a Comm and an Incident. That won't let your comms records get confused by customer.

select
i.CustomerID,
(select cus.Customer_name from Customers cus
where cus.CustomerID = i.CustomerID) as Customer,
max(c.xDate) Last_Comm_Date
from Incidents i
join Comms.c
on c.IncidentID = i.Incident_id
group by 1,2

./heLen