At 08:27 AM 22-11-01 +0200, Gerhardus Geldenhuis wrote:

>What I basically need is a distinct select from "replication_log" giving me
>all the table names and then I have a "lookup" table("replication_order")
>that assigns a weight to the tablename to sort them because they must not be
>sorted alphbetically. I also thought I needed a join but the manual referred
>me to the union page.
>If a join would solve the problem an example would be apprecaited.
>
>Here is typical data for the two tables
>Replication_log
>~~~~~~~~~~~~~~~
>LOGID TABLENAME UID DBACTION BRANCH
>===== ================= ================== =========== ============
> 8 MTRL_FRAMES 882 UPDATE GLEN
> 9 MTRL_FRAMES 882 UPDATE BOKSBURG
> 10 MTRL_FRAMES 882 UPDATE GLEN
> 11 MTRL_FRAMES 882 UPDATE BOKSBURG
> 12 MTRL_FRAMES 890 INSERT GLEN
> 13 MTRL_FRAMES 890 INSERT BOKSBURG
> 14 MTRL_FRAMES 890 UPDATE GLEN
> 15 MTRL_FRAMES 890 UPDATE BOKSBURG
> 16 SUPPLIERS 34 UPDATE GLEN
> 17 SUPPLIERS 34 UPDATE BOKSBURG
>
>Replication_order(would be constant most of the time)
>TABLENAME WEIGHT
>==============================
>CUSTOMERS 1
>MTRL_FRAMES 2
>MTRL_GENERAL 3
>MTRL_GLASSPRODUCTS 4
>MTRL_MISCELLANEOUS 5
>MTRL_MOUNTS 6
>MTRL_PRINTS 7
>MSFRAMES 8
>MSMOUNTS 9
>MSPRINTS 10
>MULTIPLESUPPLIERS 11
>POS_QISC 12
>POS_QISC_ITEMS 13
>POS_PAYMENTDETAIL 14
>POS_MISC_STOCK 15
>POS_WORKSORDER 16
>POS_WORKSORDER_BOOKING 17
>POS_BILLOFMATERIAL 18

It's still not clear what you want to achieve by way of output. Do you want all of the rows in the log table ordered according to their order ("weight") in the replication_order table?

If so, this will do it:

select rl.tablename, ro.weight, ....whatever else you want...
from replication_log rl
join replication_order ro
on rl.tablename = ro.tablename
order by 2

Beyond this, more info needed.

Helen

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