At 04:00 PM 21-11-01 +0200, Gerhardus Geldenhuis wrote:

>Hi have two tables:
>CREATE TABLE REPLICATION_LOG
>(
> LOGID DOM_INCFIELD NOT NULL,
> LOGTIMESTAMP DOM_TIMESTAMP,
> TABLENAME DOM_VARCHAR25 NOT NULL,
> UID DOM_VARCHAR25 NOT NULL,
> DBACTION DOM_VARCHAR25 NOT NULL,
> BRANCH DOM_VARCHAR25 NOT NULL,
> REPLICATED DOM_BOOLEAN,
> DATEREPLICATED DATE,
> CONSTRAINT PK_REPLICATION_LOG PRIMARY KEY (LOGID)
>)
>
>CREATE TABLE REPLICATION_ORDER
>(
> TABLENAME DOM_VARCHAR25 NOT NULL,
> WEIGHT INTEGER NOT NULL,
> CONSTRAINT PK_REPLICATION_ORDER PRIMARY KEY (TABLENAME, WEIGHT)
>)
>
>I want to do the following select from replication_log:
>"select distinct(tablename) from replication_log"
>then I want to do a union between that selection and
>replication_order and sort by weight. This is to achieve
>a specific order of sorting for the replication. I think
>this is the best way to do this.
>
>How do I do this. I have never used union before and the embedded-sql
>guide did not explain it in a understandable way. It assumes some
>basic knowledge which I learn as I go.

I suspect you won't be doing a union this time, either. :) A union combines congruent sets from each of the contributing queries and "stacks" their output one on top of the other. So a union between these two tables would be a set of tablenames followed by another set of tablenames, with nothing to order on but ...well....1.

What you seem to be asking for is a set of distinct table names in a certain order. If so, it's a join you need. Without knowing about the data in REPLICATION_ORDER, I can't tell if it is doable in a single SQL statement, since the primary key (TABLENAME modified by WEIGHT) seems to suggest that you wouldn't be able to get a DISTINCT tablename.

If that's the case, a dataset output from a stored procedure or requerying a view might be the only way - and you would have to decide which WEIGHT you were interested in.

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________