VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-Cart StoreFront Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 General help me questions
 Need help with SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Daniel Groulx
Starting Member

41 Posts

Posted - March 05 2010 :  19:12:53  Show Profile  Reply with Quote
I would like to do a sql query to get the email address of customers who bought product xyz. Looks like this would involve 3 tables oitems, orders, customers. I've tried googling for examples, but was only able to find basic single table queries.

Senior Member

United Kingdom
1898 Posts

Posted - March 06 2010 :  05:46:55  Show Profile  Visit devshb's Homepage  Reply with Quote
select customers.email
from orders,oitems,customers,products
where orders.orderid=oitems.orderid
and oitems.catalogid=products.catalogid
and orders.ocustomerid=customers.contactid
and products.ccode='XYZ'
group by customers.email
order by customers.email

(the join to products is optional, only needed if you want to search by product code instead of catalogid)

The "group by" bit is just an alternative for a "distinct" select clause, ie it ensures you don't get duplicate rows being shown.

There are other ways to do it (you could use an exists clause instead and just select from the customers table), but the above is the simplest because you can select/restrict the values from any of those tables (products, orders, customers, oitems).

That'll get the distinct list of emails of those who bought (or tried to buy) the product with a code of 'XYZ' but it'll also include orders that weren't completed/paid.
You might also want to add extra "and" bits for things like the maillist flag etc.
Use the customers.email value not the orders.oemail value, that way you get the most recent one for the customer (the orders.oemail could be out of date if they've changed their profile since ordering)

If getting customer info for who's ordered what, you'd use the customers record not the customer columns on the orders table because the customers one will always be more up to date. (unless you're doing a report for tax/accounting purposes in which case you'd use the orders customer details instead)

You can do pretty much anything in queries (regardless of whether it's access, sqlserver, or mysql), it's just a case of working out the syntax for what you're after for your database type.

If you need a hand with sql syntax/logic just post to the forum like you just did; there are loads of developers here who know the various types of sql syntax.

Simon Barnaby
[email protected]
Follow us on Twitter: http://twitter.com/bigyellowzone
Web Design, Online Marketing and VPASP addons

Edited by - devshb on March 06 2010 06:06:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000