Advanced SQL Query Help Please.

[NodsDorf]

I'm trying to run a query on my SQL database that provides the following information.

 

From the Orders table

orders_id

date_purchased

billing_name

delivery_state

payment_method

 

From the Orders_Products table

products_name

products_quantity

 

From the Orders_Total table

value

 

From the Orders_Status_History table

orders_status_id

 

I understand SQL enough to where I can run any one of those queries fine. What I want however is all that information to be retrieved in 1 query so I can view it properly.

 

There are also a couple variables I'm incorporating. First I only want data on orders_id >= 17334 and I only care about orders with the orders_status_id of 3,100001,100002,100004,100007, and lastly I don't want to see orders with the value of 0

 

In my effort to get this to work I wrote the following 2 queries.

This first one gets the order number, date, name, state, payment type, and joins it with the order status.

Select o.orders_id, o.date_purchased, o.billing_name, o.delivery_state, o.payment_method,
osh.orders_status_id
From orders as o
Inner JOIN orders_status_history as osh on o.orders_id=osh.orders_id
Where o.orders_id >= '17334' and osh.orders_status_id IN ('3','100001','100002','100004','100007')
ORDER BY o.orders_id

 

This query gets order number, products name, quantity and joins it with the order total.

 

Select op.orders_id, op.products_name, op.products_quantity,
ot.orders_id, ot.value
From orders_products as op
Join orders_total as ot on op.orders_id = ot.orders_id
Where op.orders_id >='17334' and ot.value > '0'
ORDER BY op.orders_id

 

 

Okay first issue with the first query.

I'm getting multiple entries for the same order. This I believe is due to multiple order_status_history matches.

The query returns this (Sample of first few rows)

orders_id, 'date_purchasesd', 'billing_name', 'delivery_state', 'payment_type',                orders_status_id
17334, '2008-08-20 14:12:54', 'Cust Name1',   'Buenos Aires',   'Paypal Website Payments Pro', 100002
17335, '2008-08-20 14:35:01', 'Cust Name2',   'Indiana',        'Paypal Website Payments Pro', 100002
17335, '2008-08-20 14:35:01', 'Cust Name2',   'Indiana',        'Paypal Website Payments Pro', 100002
17336, '2008-08-20 20:09:51', 'Cust Name3',   'Georgia',        'Paypal Website Payments Pro', 3
17337, '2008-08-21 10:09:46', 'Cust Name4',   'Sao Paulo',      'Paypal Website Payments Pro', 100002
17338, '2008-08-21 11:42:10', 'Cust Name5',   'TX',             'PayPal Express Checkout',     100002
17339, '2008-08-21 11:42:48', 'Cust Name6',   'Cambridgeshire', 'Paypal Website Payments Pro', 3
17340, '2008-08-22 09:17:45', 'Cust Name7',   'NA',             'PayPal Express Checkout',     100001
17340, '2008-08-22 09:17:45', 'Cust Name7',   'NA',             'PayPal Express Checkout',     100001

 

Is there a way in the 1st query above to only get the final orders_status_history.orders_status_id? At times we'll mark something as delivered but then post more comments to the customer like shipping info, or additional product information which then makes another value for the order_status_history.orders_status_id. As you can see above Cust Name2 has 2 entries, as does Cust Name7.

 

I'm going to be dealing with 2000+ rows of data so multiple entries is the suck.

 

 

I'm really thinking this can't be done with an single SQL query but some people smarter then me are telling me its possible.

 

In another thread Jack_mcs (who is a tremendous help) gave me this code:

Select o.orders_id, o.date_purchased, o.billing_name, o.delivery_state, o.payment_method, 
op.products_name, op.products_quantity, ot.value, osh.orders_status_id From
orders o left join  
orders_total ot on o.orders_id = ot.orders_id left join  
orders_products op on op.orders_id = o.orders_id,
orders_status_history osh
Where o.orders_id >= 17734 AND ot.value >= 0 and osh.orders_status_id 
IN ('3','100001','100002','100004','100007') Order By o.orders_id;

The code is sound but the query is for some reason really huge. Its causing Error #28 which I have found is related to the temp file size not being large enough. My host is telling me we have 1 gig of temp space available and this query is using all of it and needing more. Their suggestion was to streamline the query. I understand the code, I just don't understand what SQL does with the query to use this much space, therefore I have no idea as to what fix.

 

Well if you took the time to read this thank you, and if you can help I'd sure appreciate it.

 

Don

[♥geoffreywalton]

http://www.w3schools.com/sql/sql_groupby.asp

 

Wont solve the space problem, adding and order no > x and < y could cut it into manageable blocks.

 

HTH

[NodsDorf]

Thanks Geoffrey, I use w3bschools.com alot as is. :)

 

As far as I can tell there are no extended commands like you find in VB where you can go to the endoflist and merge delete duplicate data from SQL for display purposes.

[♥geoffreywalton]

http://www.w3schools.com/sql/sql_groupby.asp

 

Did you read the link?

[b00]

I'm trying to run a query on my SQL database that provides the following information.

...

Don

i don't have time to write your query for you, but

one idea is to use subqueries like i did for this problem:

 

 SELECT *
FROM customers c
WHERE datediff( now( ) , (

SELECT MAX( o.last_modified )
FROM orders o
WHERE o.customers_id = c.customers_id
AND o.orders_status =3 )
) >=90
AND datediff( now( ) , (

SELECT MAX( o.last_modified )
FROM orders o
WHERE o.customers_id = c.customers_id
AND o.orders_status =3 )
) <= ( 90 +14 )
AND (
datediff( now( ) , (

SELECT MAX( o.last_modified )
FROM orders o
WHERE o.customers_id = c.customers_id
AND o.orders_status =1 )
) <=90
) IS NULL

if does not help for this particular problem, maybe will help you in the future.

good luck.

:>)

[NodsDorf]

Did you read the link?

 

Actually I didn't, (just knew the site and didn't think to click) but did now. The Group By does the trick on the 1st query where I can now use it to get 1 line of data as opposed to before. THANK YOU!

[NodsDorf]

i don't have time to write your query for you, but

one idea is to use subqueries like i did for this problem:

 

 SELECT *
FROM customers c
WHERE datediff( now( ) , (

SELECT MAX( o.last_modified )
FROM orders o
WHERE o.customers_id = c.customers_id
AND o.orders_status =3 )
) >=90
AND datediff( now( ) , (

SELECT MAX( o.last_modified )
FROM orders o
WHERE o.customers_id = c.customers_id
AND o.orders_status =3 )
) <= ( 90 +14 )
AND (
datediff( now( ) , (

SELECT MAX( o.last_modified )
FROM orders o
WHERE o.customers_id = c.customers_id
AND o.orders_status =1 )
) <=90
) IS NULL

if does not help for this particular problem, maybe will help you in the future.

good luck.

:>)

 

Thank you for this.. it may come in handy for something in the future.

[NodsDorf]

Whelp I finally got it to work well enough.

 

This code will get

Orders_ID which is your osc order number

The customers name

The customers state (for tax reasons)

The payment method used (helps figure fees associated with your gateway processor)

The products model

The products name

The products price

The products quantity sold

The order total value (this is the final value collected + tax, - discounts ect..)

The order status (this can be used to figure if the order was refunded, denied, returned or canceled)

 

Select o.orders_id, o.customers_name, o.customers_state, o.payment_method,
op.products_model, op.products_name, op.products_price,
op.products_quantity, ot.value, o.orders_status
From orders as o
Inner join orders_products as op on o.orders_id=op.orders_id
Inner join orders_total as ot on o.orders_id=ot.orders_id
Where o.orders_id >= 17334 and ot.title = 'Total:'
Order By op.orders_id

 

Quick note (a). The "Where" clause above is specific to my needs. You may want to change it for your needs. IE if you want all orders just remove

Where o.orders_id >= 17334 and ot.title = 'Total:'

and replace with

Where ot.title = 'Total:'

 

Quick note (B) the code will generate multiple entries for orders which contain 2 or more different products. I don't see any easy way around this but it really isn't to big of a deal. The important part about this however is that the order total (displayed in the value field) will be the same for each order. In my case I just imported this to an excel file and highlighted orders with multiple entries and zeroed out the first instances of the value field and left the last one in tact. This allows me to just use the auto-sum feature in excel to find the total sales of all items.

 

Thank you all for your help support and ideas. After 5 days I finally got this to work well enough to manage.

 

 

 

Things I tried (osfinacials) there is no good info on how to install this and link it to your database. In my case I was getting HTTP /1.1 Error 406 when trying to connect.

I also tried Synkronizer which merges 2 or more excel database together. It kinda does somethings but still has issues with duplicate key values.

 

Eventually I plan on taking this into VB. I just need to find my install disk since I recently moved.