Hi Maya,
here's a trigger-solution, fast

Without triggers, I suggest U add a ClientID-
field in the comments-table, making your
query just a 2:table-join.(with aggregates though..)

trigger soulution below..

all the best

//t

CREATE TABLE CLIENTS
(
ClientID integer,
Name varchar(50),
primary key (ClientID)
);

CREATE TABLE INCIDENTS
(
IncidentID integer,
ClientID integer,
Name varchar(50),
CommentsMaxWritten date,
primary key (IncidentID)
);

CREATE TABLE COMMENTS
(
CommentID integer,
IncidentID integer,
ClientID integer,
Comment varchar(50),
Written date,
primary key (CommentID)
);

CREATE INDEX IDX_CLIENTID ON INCIDENTS (ClientID);
CREATE INDEX IDX_INCIDENTID ON COMMENTS (IncidentID);

CREATE TRIGGER UPDCOMMENTSMAX ACTIVE
AFTER INSERT POSITION 0
AS BEGIN
/*update the indicdents table */
update incidents set CommentsMaxWritten = NEW.WRITTEN WEHRE
IncidenID=New.INCIDENTID;
END^
SET TERM ; ^
4 nov 2008 kl. 11.15 skrev Maya Opperman:


Mimmo Wrote:

>Seems to me, from count of Records, that there are 1:N relationships. N

>incident and 1 customer, N Comm and 1 incident.
>Incidents.CustomerID and Comms.IncidentID are not UNIQUE indexes as you

>initially supposed.

Correct

>Anyway, comms records can't get confused by customer.

Yip, going to try Helen's statement anyway, and see what the performance
is..

--- In firebird-support@yahoogroups.com, "Maya Opperman" <maya@...> wrote:
>
> Helen's Query:
>
> 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
>
> ----------------
> As it looks for my DB:
>
> select
> i.CustomerID,
> (select cus.companyname from Customers cus
> where cus.ID = i.CustomerID) as Customer,
> max(c.communicationdate) Last_Comm_Date
> from customerincidents i
> join customercommunications c
> on c.IncidentID = i.id
> where (i.customerid = :ICustomerID)
> group by 1,2
>
> ----------------
> My current query I'm using:
>
> select first 1 * /*Note * just here for test
> purposes - will be replacing)
> from customerincidents ci
> left outer join customercommunications cc
> on cc.incidentid = ci.id
> where (ci.customerID = :ICustomerID)
> order by cc.communicationdate desc, cc.id desc
>
> ----------------
>
> Using IBExpert's performance analysis tool, both queries are reading
> through all comms and all incidents for my selected customer.
> I'm using one of my more talkative customers, who have 329 incidents and
> 2458 comms and it's pretty quick 782 mille-seconds). I'm just worried
> that I'm going to cause speed issues sooner or later by reading through
> so many records.
>
> If I had the customer id in the comms table, this query could be reduced
> to only reading one record.
>
> PS. I'm using FB 1.5. AFAIK using FB 2 won't affect index use at all
> (because there's still no index FB2 can use either)
>