QUERY IN ACCESS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am working on a database with employee details in Access. I need to write
a outer join query. But I know access does not support outer join query, is
there any other code which is equal to outer join? If anyone can send a
sample that would be a real help.

Thanks,
 
Hi,

I am working on a database with employee details in Access. I need to write
a outer join query. But I know access does not support outer join query, is
there any other code which is equal to outer join? If anyone can send a
sample that would be a real help.

Thanks,
Click to expand...

Access does support Left Outer Join and Right Outer Join queries - and
you can simulate a Full Outer Join by combining these with UNION:

SELECT TableA.*, TableB.*
FROM TableA LEFT JOIN TableB
ON TableA.fieldname = TableB.fieldname
UNION
SELECT TableA.*, TableB.*
FROM TableA RIGHT JOIN TableB
ON TableA.fieldname = TableB.fieldname;


John W. Vinson[MVP]
 
Assuming (erroneously?) no LEFT/RIGHT OUTER JOIN syntax

<<Customers LEFT OUTER JOIN Orders>>:

SELECT C1.CustomerID, O1.OrderID
FROM Customers AS C1,
Orders AS O1
WHERE C1.CustomerID = O1.CustomerID
UNION ALL
SELECT C1.CustomerID, NULL
FROM Customers AS C1
WHERE NOT EXISTS (
SELECT *
FROM Orders AS O1
WHERE C1.CustomerID = O1.CustomerID);

<<Customers FULL OUTER JOIN Orders>>:

SELECT C1.CustomerID, O1.OrderID
FROM Customers AS C1,
Orders AS O1
WHERE C1.CustomerID = O1.CustomerID
UNION ALL
SELECT C1.CustomerID, NULL
FROM Customers AS C1
WHERE NOT EXISTS (
SELECT *
FROM Orders AS O1
WHERE C1.CustomerID = O1.CustomerID);
UNION ALL
SELECT NULL, O1.OrderID
FROM Orders AS O1
WHERE NOT EXISTS (
SELECT *
FROM Customers AS C1
WHERE C1.CustomerID = O1.CustomerID);

Jamie.

--
 
Back
Top