Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
How to left outer join 3 or more dbf files (Read 5568 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
How to left outer join 3 or more dbf files
18.02.10 at 09:22:22
 
How to left outer join 3 or more dbf (dbase) files
 
Quote:

I want to ask question about my problem. I use dbf files. And I tried to relate some dbf files . For Example

SELECT KOD
, a.STOK_KOD
, a.TARIH
 FROM a
    LEFT OUTER JOIN b ON b.STOK_KOD=a.STOK_KOD  
    LEFT OUTER JOIN c  ON c.KOD=a.KOD AND
c.REFNO1=a.REFNO1
;

This code is not working but when I wrote fallowing code, it is working

SELECT KOD
, a.STOK_KOD
 FROM a
    LEFT OUTER JOIN b ON b.STOK_KOD=a.STOK_KOD  
;

If you write second Left OUTER JOIN it is not working. I tried another dbf files , always It has not worked. What can I do for to at least with 3 dbf files ?

 
It seems you are not allowed to make more than one outer join per SQL statement. However there is an easy workaround.
 
To test your scenario I made 3 dbf files using Excel actually. I just typed the following data in Excel and saved the files as a.dbf, b.dbf and c.dbf respectively (all in the same directory). I omitted the row with id=3 deliberately in tables b and c to be able to experiment with the outer join.
 
File contents:
 
a.dbf
ID      DATA_A
1      aa
2      bb
3      cc

 
b.dbf
ID      DATA_B
1      11
2      22

 
c.dbf
ID      DATA_C
1      a1
2      b2

 
To connect to these 3 database tables is easy. Just use SQL*XL's connection wizard and select dbase as the database. SQL*XL will ask for the directory where the tables can be found. Just walk though the wizard accepting all default choices and you will have your database connection. It is as easy as that!
 
Queries:
The following query works fine, linking all rows from all tables:
 
select * from a,b,c where a.id = b.id and a.id = c.id;
 
Unfortunately this query will not retrieve the row with id=3 as it is not present in tables b and c.
 
To be able to do that you can use outer joins but you can, as you found out, only use one outer join at a time.  
So this works:
 
select * from a outer join b on a.id = b.id
 
and the following fails:
 
select * from a outer join b on a.id = b.id outer join c on a.id = c.id
 
 
Now, the solution to your problem. Just change the SQL so it looks like this:
 
select a.id
,        a.data_a
,        (select b.data_b from b where a.id = b.id)
,        (select c.data_c from c where a.id = c.id)
from a
Back to top
 
« Last Edit: 18.02.10 at 09:34:42 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1