SQL Querying multiple tables using JOIN - Northwind

Question

//Personal understanding, not a hw assignment So in the sample db northwind from MS there are the tables: orders o, [order details] od, customers c

• o has orderID, customerID (inc. duplicates)
• od has orderID (inc. duplicates), unitprice, quantity, discount
• c has customerID, companyName

roughly speaking, I want to join on

o.customerID = c.customerID; selecting companyName ///
          join on o.orderID = od.orderID; selecting unitprice, quantity, discount

my end goal is to

 sum(q (up - d)) AS 'Order Total' group by od.orderID then
                  sum(Order Total) group by companyName(?)

My main issue is not know how/what to join properly though.

Thanks in advance

Answer 1

Check out your scenario on SQL Fiddle

SELECT comp.`company_name` AS 'company',COUNT(DISTINCT o.id_sales_order) AS 'total_orders',SUM(`unit_price`) AS 'grand_total'
FROM sales_order AS o
LEFT JOIN sales_order_item AS od ON od.fk_sales_order = o.id_sales_order
LEFT JOIN customer AS c ON c.id_customer = o.fk_customer
LEFT JOIN company AS comp ON comp.id_company = c.fk_company_id
GROUP BY comp.`company_name`

hope this what you are looking for

Answer 2

Assuming that you create the correct Select statement as you required, the join part should be something like:

From Orders o join Order_Detail od on o.orderID = od.orderID join Customer c on o.customerID = c.customerID

Types of join can be: join, inner join, right/left join. It depends on you what do you want to achive i.e if you want to exclude/include null references. But the structure is the same.