select distinct email_address
from people P,registered_products A,registered_products B
where P.person_id = A.person_id
and A.product_type = "Product A"
and P.person_id = B.person_id
and B.product_type = "Product B"
;
Give it a try !!!
----- Original Message -----
From: "Benjamin Ventura" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Thursday, August 2, 2007 1:22:00 PM (GMT-0500) America/New_York
Subject: Complex query
I have a database tracking registrations of software products with two related
tables, "registered_products" and "people". I need to pull a list of email
addresses from the people table BUT I only want to pull people records who own
very specific combinations of products from the registered_products table.
Let's say I want people who own both Product A AND Product B... My first guess
is to write a query like this:
select
distinct email_address
from
people
join
registered_products on people.person_id = registered_products.person_id
where
registered_products.product_type = "Product A" and
registered_products.product_type = "Product B"
However, this does not work, no rows are returned. When I think about it, that
makes sense, because rows are evaluated one at a time, and no single product
row can simultaneously have two values for the column "product_type".
I need to only get results from a person record that has two related
registered_product rows, one with a value of "Product A" and one with "Product
B".
What is the best way to write a query to do this?
Thanks,
Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
