FULL JOIN

D

Darren

I am having a problem with a full join, I have been using the following
example:

SELECT d.* ,a.salary
FROM tbldetails d FULL JOIN tblAccounts a
ON d.EID = a.eid

When I run the is querie I get the following message: Syntex Error in From
clause

However when i chage the FULL to a LEFT OR RIGHT it works perfectly.
Please help.
 
O

Ofer

Use Inner join

SELECT d.* ,a.salary
FROM tbldetails d INNER JOIN tblAccounts a
ON d.EID = a.eid

When you want only the records that are match in both tables.
 
O

Ofer

If you want all the records from tbldetails with tha matching recprds from
tblAccounts, so the records from tbldetails will still be displayed, then use
LEFT join

SELECT d.* ,a.salary
FROM tbldetails As d LEFT Join tblAccounts As a
ON d.EID = a.eid
 
J

John Spencer

Access does not support FULL OUTER JOINS. If that is what you need you will
have to use 3 queries

A Left Join
A Right Join
And then UNION ALL to return the records you want.
 
M

Michel Walsh

Hi,

or the reverse:

start with a UNION on all possible EID,
then two left joins (preserving all EID).


SELECT eid
FROM d
UNION
SELECT eid
FROM a


saved as QU1 (why not making a table and index the eid values), then



SELECT ...
FROM (qu1 LEFT JOIN d ON qu1.eid=d.eid) LEFT JOIN a ON qu1.eid=a.eid




Hoping it may help,
Vanderghast, Access MVP
 
Top