CODEWITHSUNDEEP
mysqlsql
moto
moto

Reputation: 940

Joining 3 tables and adding a total column in MySQL

I have three tables in a MySQL database

Table 1(vendors) contains all of my vendors

Table 2(puchaseorders) contains all of my purchaseorders and with a common column being 'vendor'

Table 3 contains all of the individual items from those orders with a common column being purchase_order

In the vendors table I have a historical balance that I want to add the running total to.

This is what I have right now. It works perfect but it only returns the vendors that are in the purchaseorders table.

I want to return all of the vendors in the vendors table. If there isn't a "running_total" don't do anything to the balance.

SELECT *, vendors.balance + vend.running_total AS total FROM vendors 
INNER JOIN 
(SELECT vendor, SUM(orderhistory.qty_received *                         
orderhistory.estimated_price) AS running_total FROM orderhistory 
INNER JOIN purchaseorders ON 
orderhistory.purchase_order=purchaseorders.purchase_order
GROUP BY purchaseorders.vendor) AS vend ON vend.vendor=vendors.vendor;

Upvotes: 0

Views: 63

Answers (2)

Vash
Vash

Reputation: 1787

You can change the join outside to left join and use a coalesce around vend.running_total field so that it is treated as 0 if it is not present in the inner table and as a non-zero value if present. Try: 

SELECT *, vendors.balance + COALESCE(vend.running_total,0) AS total 
FROM vendors 
LEFT JOIN 
(SELECT vendor, SUM(orderhistory.qty_received * orderhistory.estimated_price) AS running_total 
FROM orderhistory INNER JOIN purchaseorders 
ON orderhistory.purchase_order = purchaseorders.purchase_order
GROUP BY purchaseorders.vendor) AS vend 
ON vend.vendor=vendors.vendor;

Upvotes: 1

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following (LEFT JOIN and IFNULL)

SELECT *, vendors.balance + IFNULL(vend.running_total,0) AS total
FROM vendors 
LEFT JOIN 
  (
    SELECT vendor, SUM(orderhistory.qty_received * orderhistory.estimated_price) AS running_total
    FROM orderhistory 
    INNER JOIN purchaseorders ON orderhistory.purchase_order=purchaseorders.purchase_order
    GROUP BY purchaseorders.vendor
  ) AS vend
ON vend.vendor=vendors.vendor;

Upvotes: 1

Related Questions