Subject Re: [firebird-support] Possible to have an index across 2 tables?
Author tjelvar eriksson
Hi Maya,
my previous post didn't seem to get through,
here's a trigger-solution, result is f*a*s*t.

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..
>
>



[Non-text portions of this message have been removed]