snubbr.com

In domain name user language what is Verisign SSL and DNS ?

Get a Verisign SSL certificate for 30 days FREE. Click here to use coupon...

Get a free trial of Verisign SSL certificates. Click here for this special deal...
My 1st question is: In domain name user language what is Verisign SSL and DNS ? Hoping for any comment. Another question on my mind: 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 Verisign website Payments Pro', 100002.

17335, '2008-08-20 14:35:01', 'Cust Name2', 'Indiana', 'Paypal Verisign website Payments Pro', 100002.

17335, '2008-08-20 14:35:01', 'Cust Name2', 'Indiana', 'Paypal Verisign website Payments Pro', 100002.

17336, '2008-08-20 20:09:51', 'Cust Name3', 'Georgia', 'Paypal Verisign website Payments Pro', 3.

17337, '2008-08-21 10:09:46', 'Cust Name4', 'Sao Paulo', 'Paypal Verisign 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 Verisign 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 it's 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..

Comments (68)

Hmm... I need to find out myself. I don't know what is the answer. I'll do some research in Google and get back to you if I find an answer. You should email the people at Verisign as they probably could give you an answer..

Comment #1

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...

Comment #2

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..

:>)..

Comment #3

Actually I didn't, (just knew the Verisign 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!..

Comment #4

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

Comment #5

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 (.

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..

This post has been edited by.

NodsDorf.

: 02 February 2009, 23:20..

Comment #6

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 Verisign website Payments Pro', 100002.

17335, '2008-08-20 14:35:01', 'Cust Name2', 'Indiana', 'Paypal Verisign website Payments Pro', 100002.

17335, '2008-08-20 14:35:01', 'Cust Name2', 'Indiana', 'Paypal Verisign website Payments Pro', 100002.

17336, '2008-08-20 20:09:51', 'Cust Name3', 'Georgia', 'Paypal Verisign website Payments Pro', 3.

17337, '2008-08-21 10:09:46', 'Cust Name4', 'Sao Paulo', 'Paypal Verisign 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 Verisign 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 it's 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..

Comment #7

Http://www.w3schools...sql_groupby.asp.

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

HTH..

Comment #8


This question was taken from a support group/message board and re-posted here so others can learn from it.

 

Categories: Home | Diet & Weight Management | Vitamins & Supplements | Herbs & Cleansing |

Sexual Health | Medifast Support | Nutrisystem Support | Medifast Questions |

Web Hosting | Web Hosts | Website Hosting | Hosting |

Web Hosting | GoDaddy | Digital Cameras | Best WebHosts |

Web Hosting FAQ | Web Hosts FAQ | Hosting FAQ | Hosting Group |

Hosting Questions | Camera Tips | Best Cameras To Buy | Best Cameras This Year |

Camera Q-A | Digital Cameras Q-A | Camera Forum | Nov 2010 - Cameras |

Oct 2010 - Cameras | Oct 2010 - DSLRs | Oct 2010 - Camera Tips | Sep 2010 - Cameras |

Sep 2010 - DSLRS | Sep 2010 - Camera Tips | Aug 2010 - Cameras | Aug 2010 - DSLR Tips |

Aug 2010 - Camera Tips | July 2010 - Cameras | July 2010 - Nikon Cameras | July 2010 - Canon Cameras |

July 2010 - Pentax Cameras | Medifast Recipes | Medifast Recipes Tips | Medifast Recipes Strategies |

Medifast Recipes Experiences | Medifast Recipes Group | Medifast Recipes Forum | Medifast Support Strategies |

Medifast Support Experiences |

 

(C) Copyright 2010 All rights reserved.