List:General Discussion« Previous MessageNext Message »
From:Benjamin Ventura Date:August 2 2007 5:37pm
Subject:Re: Complex query
View as plain text  
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
>
>
>  
>

Thread
Complex queryBenjamin Ventura2 Aug
  • Re: Complex queryRolando Edwards2 Aug
    • Re: Complex queryBenjamin Ventura2 Aug
  • Re: Complex queryBaron Schwartz2 Aug