From: Benjamin Ventura (benbias-inc.com)
Date: Thu Aug 02 2007 - 12:22:00 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]