LU06c - SQL-DQL: Select from multiple tables

Source: W3Schools.co: SELECT with WHERE

As we know, a RDB essentially consists of tables which are linked together to increase quality and performance and reduce unintended redundancies and inconsistencies. The following ERD shows such a data structure of a shop including some details.

If we want to know which orders were placed by which customer we have to deal with the two tables CUSTOMERS and ORDERS, and the key that connects the two is logically the CustomerID.

In practice, we would like to know which customer, e.g. with ID =3, has ordered which product. The following SQL statement would read as follows:

SELECT c.*, o.orderdate 
FROM CUSTOMERs c, ORDERS o
WHERE c.CustomerID = 3
AND c.CustomerID = o.CustomerID;

In the line FROM CUSTOMER c, ORDERS o, we use an alias for the tablename. This is practical because we can use the alias instead of the full table name in the rest of the statement. The result of the SQL statement you find below

The basic idea how to receive data from two table shows us the way how we can retrieve data from three or more tables, as the approach is similar. In that case the three or more tables require a common ID, that linkes the tables involved. The requirements could contain some order details, e.g. which product and the which quantity the customers had ordered. The according SQL select statement would be like:

SELECT c.CustomerID, c.CustomerName, o.Orderdate, od.ProductID, od.Quantity 
FROM Customers c, Orders o, OrderDetails od
WHERE o.orderid = od.orderID
AND c.CustomerID = o.CustomerID
AND c.CustomerID = 2;

The result is shown in the figure below:

Volkan Demir