|
Daniel Groulx
Starting Member
41 Posts |
 Posted - March 05 2010 : 19:12:53
|
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.
|
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - March 06 2010 : 05:46:55
|
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
Developer
[email protected]
www.BigYellowZone.com
www.BigYellowKey.com
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 |
 |
|
All Forums
Need help with SQL query
New Topic
Printer Friendly
Topic 
TrustGuard - PCI Security Scanner
