Hi Benjamin,
Benjamin Ventura wrote:
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"
Since you are selecting individual values from one table, and the related table has
multiple records, that's a hint to group the related table and join the grouped result
to the un-grouped people table. This way you solve the problem in steps: a) find
person_ids who have more than one product b) find the information desired about each
person_id. I'd try something like this:
select email_address
from people
join (
select person_id
from registered_products
group by person_id
having count(distinct product_type) > 1
) as der using(person_id);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
