Need to do a full outer join

G

Grd

Hi there,

I can do a left and right outer join but there is no option in access to do
a full outer join.
Is that possible in access?

Any help will be greatly appreciated.

Suzan
 
G

geebee

hi,

You would create a UNION query, combining the results from multiple queries,
as OUTER JOIN is not allowed in Access. To create a pseudo OUTER JOIN, you
could try something like:

SELECT Employees.LastName, Employees.City, Suppliers.CompanyName,
Suppliers.City
FROM Employees INNER JOIN Suppliers ON Employees.City = Suppliers.City
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName,
Suppliers.City
FROM Employees LEFT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE (((Suppliers.City) Is Null))
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName,
Suppliers.City
FROM Employees RIGHT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE Employees.City is null;

In this example, the first query is an INNER JOIN. The second is a LEFT
JOIN, and the last query is a RIGHT JOIN. You could use this in practice
with your own query.

Hope this helps,
geebee
 
G

Grd

that works

Thanks


geebee said:
hi,

You would create a UNION query, combining the results from multiple queries,
as OUTER JOIN is not allowed in Access. To create a pseudo OUTER JOIN, you
could try something like:

SELECT Employees.LastName, Employees.City, Suppliers.CompanyName,
Suppliers.City
FROM Employees INNER JOIN Suppliers ON Employees.City = Suppliers.City
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName,
Suppliers.City
FROM Employees LEFT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE (((Suppliers.City) Is Null))
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName,
Suppliers.City
FROM Employees RIGHT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE Employees.City is null;

In this example, the first query is an INNER JOIN. The second is a LEFT
JOIN, and the last query is a RIGHT JOIN. You could use this in practice
with your own query.

Hope this helps,
geebee
Click to expand...
 
M

Michel Walsh

Hi,


Removing the first inner join, and, in the first outer join, removing the
where clause, would also work, and the new solution would then be with one
less join (so probably faster).


Vanderghast, Access MVP
 
Top