Brilliant, this one works perfect! Thank you for the quick reply!
Ben
Rolando Edwards wrote:
>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" <ben@stripped>
>To: mysql@stripped
>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
>
>
>
>
