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