how to join multiple tables in a database to combine data from different sources with example

Example:

Consider a database with the following tables:

1. Customers

   customer_id	customer_name	country
   1	Alice	USA
   2	Bob	UK
   3	Charlie	Canada

2. Orders

   order_id	customer_id	order_date	total_amount
   101	1	2024-01-15	250.50
   102	2	2024-02-10	300.75
   103	1	2024-03-20	150.30

3. Products

   product_id	product_name	price
   1	Laptop	1000
   2	Headphones	150
   3	Mouse	50

4. Order_Details

   order_id	product_id	quantity
   101	1	1
   101	2	2
   102	3	3
   103	1	1

Goal:

You want to get a list of customers, their order details (including products and total order value), and the corresponding product names.

SQL Query Example (Multiple Joins):

sql
SELECT 
    c.customer_id,
    c.customer_name,
    c.country,
    o.order_id,
    o.order_date,
    p.product_name,
    od.quantity,
    (p.price * od.quantity) AS total_product_value
FROM 
    Customers c
JOIN 
    Orders o ON c.customer_id = o.customer_id
JOIN 
    Order_Details od ON o.order_id = od.order_id
JOIN 
    Products p ON od.product_id = p.product_id
ORDER BY 
    o.order_date;

Explanation of the Query:

• JOINs:
  • Customers c is joined with Orders o on customer_id.
  • Orders o is joined with Order_Details od on order_id.
  • Order_Details od is joined with Products p on product_id.
• Output:
  • For each order, you'll get the customer_id, customer_name, order_id, order_date, product_name, quantity, and the calculated total value for each product (total_product_value).

Result:

customer_id	customer_name	country	order_id	order_date	product_name	quantity	total_product_value
1	Alice	USA	101	2024-01-15	Laptop	1	1000
1	Alice	USA	101	2024-01-15	Headphones	2	300
2	Bob	UK	102	2024-02-10	Mouse	3	150
1	Alice	USA	103	2024-03-20	Laptop	1	1000

This is a simple example, but you can adapt it depending on your specific table structure and relationships. Let me know if you need further clarification or more advanced examples!