Join 3 tables in DBF using SQL

Old school programs and databases, they just cannot get enough of it. Here is this scenario where Clippers is still in the industry. As migration to new systems do take time, this is where a temporary solution was needed by the internal staff to do some SQL Query commands to be displayed on the webpage.

Data from tables in DBF files needed to be retrieved. There is a way which is to get data from each table and then compare it with the next SQL statement. Time and resources consuming, it was not feasible. Then a join SQL statement was then mentioned. Genius. Problem solved.

dbf tables

That was the case until suddenly there was a request to get another field from another table in the DBF file. Using double join statements does not seem to work at all. Talk about solving it the fastest way. Thus looking around, it seems that instead of doing a join statement, why not do a separate select binding statement? What is that? Have a look below.

$query = "select
dn.short_nm as NAME,
dd.detail as PARTICULAR,
dn.notesno as NOTE_NO,
dn.notes_dt as NOTE_DT,
(select cl.client_nm from client.dbf cl where dn.short_nm = cl.short_nm) as LONGNAME,
dn.type as TYPE
from dnotes.dbf dn
left join dnotesdt.dbf dd on dd.notesno = dn.notesno
where dn.notes_dt = {D '".$changeDate."'}" ;

Noticed how many tables were called? Yes, there are 3 of them. Finally problem solved.

Leave a Reply