MS Access outer join problem
L.Y. Lam
I have 2 tables.
Table1:
key1 value1 select1
1 001 STF
2 002 STF
3 003 SI
Table2:
key2 value2
1 Value001
3 Value003
4 Value004
Goal: I want to include all Table2 entries and only those Table1 matches.
Solution: Table2 left outer join to Table1
key2 value2 value1 select1
1 Value001 001 STF
3 Value003 003 SI
4 Value004
New Goal: However, I want the only those table1 rows with select1 value
equals to 'STF'.
Result: When I add a selectin criteria like the followings, I lost a record
with key value 3.
SELECT Table2.key2, Table2.value2, Table1.value1, Table1.select1
FROM Table2 LEFT JOIN Table1 ON Table2.key2 = Table1.key1
WHERE (((Table1.select1) Is Null Or (Table1.select1)="STF"));
key2 value2 value1 select1
1 Value001 001 STF
4 Value004
However, I want the result like the following shown. Just like the result
produced by the following outer join SQL in Oracle's outer join syntax.
key2 value2 value1 select1
1 Value001 001 STF
3 Value003
4 Value004
SELECT Table2.key2, Table2.value2, Table1.value1, Table1.select1
FROM Table2, Table1
WHERE Table2.key2 = Table1.key1 (+)
and Table1.select1 (+)= 'STF';
As I can observed, MS Access do the join, and then apply the selection
criteria. Oracle does the other way round, she applies the selection
criteria in each table and then does the join. I have scanned through a few
Access books about the topic. They talk little, if not none, about it.
Can you suggest a solution that does achieve it elegantly?
If I know the solution, I promise I will post it here to share with you.
Regards.
Happy newsgroup.
L.Y. Lam
first message here. Partial solution was found with new problem specified.
*}
In my working environment, we use back-end Oracle database and access data
through MSODBC 3.5 with MS Access'97 as the client front-end.
The following shows the Oracle query result to be emulated in Access.
=============================================================
-- Oracle outer join:
create table lyl_tableMstr (
key varchar2(2), value varchar2(10) );
insert into lyl_tableMstr values('1', 'Mstr001');
insert into lyl_tableMstr values('3', 'Mstr003');
insert into lyl_tableMstr values('4', 'Mstr004');
create table lyl_tableRef (
key varchar2(2), value varchar2(10), SelectKey varchar2(10) );
insert into lyl_tableRef values('1', '001', 'STF');
insert into lyl_tableRef values('2', '002', 'STF');
insert into lyl_tableRef values('3', '003', 'SI');
select m.key, m.value, r.value, r.SelectKey
from lyl_tableMstr m, lyl_tableRef r
where r.key = m.key;
KE VALUE VALUE SELECTKEY
-- ---------- ---------- ----------
1 Mstr001 001 STF
3 Mstr003 003 SI
select m.key, m.value, r.value, r.SelectKey
from lyl_tableMstr m, lyl_tableRef r
where r.key (+)= m.key;
KE VALUE VALUE SELECTKEY
-- ---------- ---------- ----------
1 Mstr001 001 STF
3 Mstr003 003 SI
4 Mstr004
select m.key, m.value, r.value, r.SelectKey
from lyl_tableMstr m, lyl_tableRef r
where r.key (+)= r.key
and r.SelectKey (+)= 'STF';
KE VALUE VALUE SELECTKEY
-- ---------- ---------- ----------
1 Mstr001 001 STF
3 Mstr003
4 Mstr004
=============================================================
Now, in Access, I created 2 tables with the filled content shown below.
Table1 (reference):
key1 value1 select1
1 001 STF
2 002 STF
Table2 (master):
key2 value2
1 Value001
3 Value003
4 Value004
Goal: I want to include all Table2 entries and only those Table1 matches.
Solution: Table2 left outer join to Table1
key2 value2 value1 select1
1 Value001 001 STF
3 Value003 003 SI
4 Value004
New Goal: However, I want only those table1 rows with select1 value
equals to 'STF'.
Result: When I add a selection criteria like the followings, I lost a record
with key value 3.
SELECT Table2.key2, Table2.value2, Table1.value1, Table1.select1
FROM Table2 LEFT JOIN Table1 ON Table2.key2 = Table1.key1
WHERE (((Table1.select1) Is Null Or (Table1.select1)="STF"));
key2 value2 value1 select1
1 Value001 001 STF
4 Value004
However, I want the result like the following shown. Just like the result
produced by the following outer join SQL in Oracle's outer join syntax.
key2 value2 value1 select1
1 Value001 001 STF
3 Value003
4 Value004
SELECT Table2.key2, Table2.value2, Table1.value1, Table1.select1
FROM Table2, Table1
WHERE Table2.key2 = Table1.key1 (+)
and Table1.select1 (+)= 'STF';
As I can observed, MS Access do the join, and then apply the selection
criteria. Oracle does the other way round, she applies the selection
criteria in each table and then does the join. I have scanned through a few
Access books about the topic. They talk little, if not none, about it. The
book "Access 97 Developer's Handbook" from SYBEX does suggest making an
extra query for sorting out the wanted rows at the reference query before
applying outer-join. Yes, it does work for local database, however, it
doesn't in server database like Oracle.
For local MDB table:
Create a query Query21:
SELECT Table1.key1, Table1.value1, Table1.select1
FROM Table1
WHERE (((Table1.select1)="STF"));
key1 value1 select1
1 001 STF
2 002 STF
Create a query Query22:
SELECT Table2.key2, Table2.value2, Query21.value1, Query21.select1
FROM Table2 LEFT JOIN Query21 ON Table2.key2 = Query21.key1;
key2 value2 value1 select1
1 Value001 001 STF
3 Value003
4 Value004
For the equivalent queries connecting Oracle database tables through the
MSODBC 3.5:
The first query yields the same result.
However the second query produce these:
KEY ValueM ValueF SELECTKEY
1 Mstr001 001 STF
It seems that the outer-join stops functioning.
Can you suggest a solution that does achieve it elegantly?
Regards.