outer join in access

Question

I've 3 tables in total, namely table1, table2 & 3.

Data in table2 & 3 are quite huge, so I wanna outer join table2 & 3 based on the keys e.g. member no & id no first before joining table1.

    SELECT 
A.field1, 
A.field2, 
A.field3, 
A.field20,

B.specialfield1, B.specialfield2,

C.specialfield7, B.specialfield8

FROM (table1 AS A LEFT JOIN table2 AS B 
ON (A.field1 = B.field1) 
AND (A.field2 = B.field2) 
AND (A.field3 = B.field3)) 
LEFT JOIN table3 AS C 
ON (A.field1 = C.field1) 
AND (A.field2 = C.field2) 
AND (A.field3 = C.field3))

What can I do? I m using Access97 and seems syntax I knew from SQL server may not work right there. Thanks in advance!!

Cheers VB

score 0 · Answer 1 · 2013-09-10T07:07:51.740

0

What are the datatypes of the fields you are joining upon? That would help us get an idea.

Use HASH JOIN if your big tables are both indexed or both not indexed. Its performance also increases Here is the link: http://www.sqlserverblogforum.com/2011/10/merge-join-vs-hash-join-vs-nested-loop-join/

edited Sep 10 '13 at 07:07

answered Sep 10 '13 at 06:49

score 0 · Answer 2 · answered Sep 10 '13 at 06:49

um.. I believe this should work:

SELECT Table1.[field1], Table1.[field2], Table1.[field3], Table2.[field 1], Table3.[field 2]
FROM (Table1 INNER JOIN Table2 ON Table1.sno = Table2.fk_table1) INNER JOIN Table3 ON Table1.sno = Table3.fk_table1;

Note: Table1.sno is the primary key.

outer join in access

2 Answers2