need help creating sql query

[maxhr]

Hi,

I'm trying to create an sql query that will return the following info:

 

orders_total:orders_id, orders:delivery_city, and orders_total:value (Sub_Total) only for orders where sales tax has been charged.

 

I've already written a query that will return the order number and delivery city, but I can't figure out how to get the order subtotal to appear in the listing.

 

Here is my query so far:

 

SELECT DISTINCT c.orders_id, cd.delivery_city

FROM orders_total c, orders cd

INNER JOIN orders

ON c.orders_id = cd.orders_id

WHERE c.value <> 0 AND c.title = 'Tax:' AND c.orders_id > 12345 limit 0,1000;

 

where 12345 is the order I want to start with.

 

I use this report to help prepare my sales tax return and need the order sub-total value displayed along with the order number and delivery city.

 

Currently I print out the report and then go into each order one by one and grab the subtotal and write it down next to the query results.

 

Any help would be appreciated.

 

thanks,

 

Max

[♥geoffreywalton]

You need to get 2 records per order

 

SELECT o.orders_id, o.date_purchased, o.delivery_name, o.delivery_country, o.billing_name, o.billing_country, ot.value AS order_total, ott.value AS order_tax

FROM orders o

LEFT JOIN orders_total ot ON o.orders_id = ot.orders_id

LEFT JOIN orders_total ott ON o.orders_id = ott.orders_id

WHERE o.orders_id >1

AND ott.class = 'ot_tax'

AND ot.class = 'ot_total'

 

Looks good to me

[maxhr]

Oh, that's excellent, thanks very much. I couldn't figure out how to get 2 records for the same order.

 

I changed your query a bit to suit my exact needs, now it returns only the orders with tax and only the fields I really need:

 

SELECT o.orders_id, o.delivery_city, ot.value AS order_total

FROM orders o

LEFT JOIN orders_total ot ON o.orders_id = ot.orders_id

LEFT JOIN orders_total ott ON o.orders_id = ott.orders_id

WHERE o.orders_id > 12345

AND ott.value >0

AND ott.class = 'ot_tax'

AND ot.class = 'ot_subtotal'

LIMIT 0 , 1000

 

This will save me many hours of work each quarter.

 

many thanks,

 

Max

 

 

You need to get 2 records per order