2

I am posting for the first time.

I have been trying to figure out the issue with this report. In this report,i want to show the serial numbers for the products that were sold and not returned (+ not - price) most recently.

I want to check whether a product has been marked sold if it was sold last and not returned last.

This is how I have done, I think I may need to subquery it but I am unable to figure out the first part.

SELECT 
    ps.serial,p.upc,p.name,so.date_sold 
FROM 
    store_orders so 
    LEFT JOIN store_order_products sop ON so.id = sop.order_id
    LEFT JOIN products p ON sop.product_id = p.id 
    LEFT JOIN product_serials ps ON ps.serial = sop.product_serial
WHERE 
    (CAST(so.date_sold AS DATE) BETWEEN '2013-4-15' AND '2013-5-15')
    AND ps.deleted = '0' AND sop.price > 0
ORDER BY 
    sop.id DESC
LIMIT 10

It is returning the products that may have been returned last instead of only sold last.

`ID Serial  Price
1: 8332-83 10.00
2: 8332-83 -10.00
Dont want to see this because it was returned last, not sold last

ID Serial  Price
2: 8332-82 10.00
1: 8332-82 -10.00
This was sold last not returned so looking to see this serial`

Please help. Thanks in advance. Joe

user2402877
  • 33
  • 1
  • 6

1 Answers1

1

Ok, figured it out thanks to myself. But for anyone else with this kind of issue, here you go.

TRIPLE NESTY, lol

SELECT * FROM (
SELECT * FROM (
SELECT 
    so.id,sop.product_serial,p.upc,sop.price,p.name,so.date_sold 
FROM 
    store_orders so 
    LEFT JOIN store_order_products sop ON so.id = sop.order_id
    LEFT JOIN products p ON sop.product_id = p.id 
    LEFT JOIN product_serials ps ON ps.serial = sop.product_serial
WHERE ps.deleted = '0' AND so.store_id = '$store_id'
ORDER BY 
    sop.id DESC
LIMIT 1000
) AS t
GROUP BY t.product_serial
) AS t2
WHERE t2.price > 0
user2402877
  • 33
  • 1
  • 6