Help on MySQL query

Question

I have the following table structure:

Customers  - Cust_Orders - Cust_Items  - Cust_Payments  -  Drivers
  id           id             id            id             id
  company      cid            oid           oid            name
               driver         price         amount
               date           qty           date
                              vat

What I want to do is showing last unpaid order marked by a specific driver id + the sum of all unpaid orders for that particular customer except the order that is already selected.

Since there might be more than one cust_items & more than one cust_payments I had to use select from select as otherwise I would have wrong sums & things got messy till I reached a point I forgot what I was doing.

Any Help would be greatly appreciated.

My current SQL which lacks the final part only (sum of other unpaid orders amounts):

SELECT `customers`.`company`, 
T1.*, 
ROUND( IFNULL( SUM(`cust_payments`.`amount`), 0 ), 2) AS `paid` 
FROM (
SELECT `cust_orders`.*, 
    ROUND( IFNULL( SUM(`cust_items`.`qty` * `cust_items`.`price`), 0 ), 2) AS `total`, 
    SUM( ( `cust_items`.`price` * `cust_items`.`qty` * `vat` ) / 100) AS `vat` 
    FROM `cust_orders` 
    LEFT JOIN `cust_items` ON `cust_orders`.`id` = `cust_items`.`oid` 
    GROUP BY `cust_orders`.`id`
) `T1` 
LEFT JOIN `customers` ON `T1`.`cid` = `customers`.`id` 
LEFT JOIN `cust_payments` ON `T1`.`id` = `cust_payments`.`oid` 
WHERE `T1`.`driver` = ? GROUP BY `T1`.`id` HAVING (`T1`.`total` - `paid`) > ? 
ORDER BY `T1`.`id` DESC  LIMIT 1

rabudde · Accepted Answer · 2011-08-22 04:06:33Z

1

Can you try

SELECT
    x.id,
    x.company,
    y.id,
    y.cid,
    y.driver,
    y.date,
    @ut:=ROUND(SUM(z.qty*z.price),2) AS unpaid_total,
    @uv:=SUM((@ut*z.vat)/100) AS unpaid_vat,
    @st:=ROUND(SUM(b.qty*b.price),2)-@ut AS sum_total,
    SUM((@st*b.vat)/100)-@uv AS sum_vat
FROM Customers x
INNER JOIN Cust_Orders y ON x.id=y.cid
INNER JOIN Cust_Items z ON y.id=z.oid
LEFT JOIN Cust_Orders a ON x.id=a.cid
LEFT JOIN Cust_Items b ON a.id=b.oid
WHERE
    y.driver=? AND
    NOT EXISTS (SELECT * FROM Cust_Payments WHERE oid=y.id) AND
    NOT EXISTS (SELECT * FROM Cust_Payments WHERE oid=a.id)
GROUP BY x.id,x.company, y.id, y.cid, y.driver, y.date

edited Aug 22, 2011 at 4:06

answered Aug 21, 2011 at 17:28

rabudde

7,7826 gold badges58 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Over a year ago

This looks clearer than my example, but it's not quite what I expected. as I should end up with one unpaid order per customer + sum of other unpaid orders for that customer (excluding the order that is displayed). I will try to reconstruct my work based on your example & post it back tomorrow morning.

rabudde Over a year ago

Simply I've forgotten to include customers id?

Collectives™ on Stack Overflow

Help on MySQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related