How can I join data from different tables?-CodePudding

The Problem

I use VBA and I would like to import all the data that is available for specific employeeID from an Oracle database. Unfortunately, the needed data is contained in four different tables. Taking the w3schools-editor as an example, I am interested into the tables Shippers, Orders, Customers and Products:

Name: Shippers 
_____________
ShipperID | ShipperName | ...
_____________

Name: Orders
_____________
OrderID | EmployeeID |ShipperID ...
...
_____________

Name: OrderDetails
_____________
OrderID | ProductID | ...
...
_____________

Name: Products
_____________
ProductID | ProductName | ...
...
_____________

For a specific EmployeeID in Orders, I wish to obtain a table of the form:

Name: Goal
_____________
ShipperName | ProductID | ProductName 
...
_____________

What I tried so far:

Select Shippers.ShipperName, Orders.OrderID, OrderDetails.ProductID 
From Orders
Inner Join OrderDetails On OrderDetails.OrderID = Orders.OrderID
Inner Join Shippers On Shippers.ShipperID = Orders.ShipperID

Select Distinct Orders.OrderID, OrderDetails.ProductID 
From OrderDetails
Inner Join Orders On
Orders.OrderID = OrderDetails.OrderID And Orders.EmployeeID = 5

But in both cases I get errors.

I also tried to use union

Select ShipperName From Shippers
    Inner Join Orders On Orders.ShipperID = Shippers.ShipperID
Union
Select ProductID From OrderDetails
    Inner Join Orders On Orders.OrderID = OrderDetails.OrderID

but then I get a table with one column containg the entries from the origial two columns.

I did not manage to get any further towards Goal

Any suggestions?

PS: I hope that it is clearer now.

CodePudding user response：

let's see if this sql statement helps you:

SELECT Shippers.ShipperName, 
       Products.ProductID, 
       Products.ProductName
FROM Shippers
INNER JOIN Orders ON Shippers.ShipperID = Orders.ShipperID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID

so you can add the where condition:

WHERE Orders.employeeID = ?