Subject | Re: [firebird-support] Possible to have an index across 2 tables? |
---|---|
Author | tjelvar eriksson |
Post date | 2008-11-04T10:39:46Z |
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:
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:
>[Non-text portions of this message have been removed]
> 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..
>
>