>Hi
>
>I have two tables that I want to join together into a single, problem is they are unrelated to each other, this is to say that one file is transactions for a specific jobno so my code is:
>
>select upper(cname) as name,type,desc,date,reason,operator,startfinish,transid,JOBNO from wipdata!logfile,operators into cursor tmplog readwrite where jobno=wipdata.jobno and operator=operators.nempno order by transid
>
>The other file (PRODUCTIONLOG.DBF) is transactions not specific to a jobno but just to a daterange.
>
>Both files have the same structure but obviously jobno is empty in the PRODUCTIONLOG.DBF
>
>I'm struggling to work out what the syntax should be to join both tables together since I have no ON clause.
>
>Any help extremley appreciated
If you don't have foreign key values in ProductionLog.dbf then you're stuck. You need to somehow populate the jobno column (if that's the foreign key) so you can do your JOIN.
If you know that all transactions in ProductionLog.dbf for a specific range of datetimes is for JobX, then you can UPDATE the JobNo column for the corresponding rows in ProductionLog.DBF (use a copy of the table, to be safer) with JobX's primary key value. But if the transactions are interleaved between several jobs, you can't do that.
It sounds like a bunch of child rows in ProductionLog.dbf got created but the code didn't update the foreign key
Ask yourself if a human could relate the tables manually. Maybe one couldn't because the information just isn't there. If so, no SELECT - SQL command can magically make it happen.
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up