List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:April 15 2008 2:40pm
Subject:Find two sets of records
View as plain text  
I'm drawing a blank here.

I need to extract two sets of records from a pair of tables. `eo_name_table`
is a list of titles, `prod` is a list of products having titles and a
discontinued flag. I want all of those records from `eo_name_table` that do
not have a matching title in `prod`. I also want those records from
eo_name_table` where the ONLY matching records in `prod` are discontinued.

- The first part is easy:

	SELECT eo_name_table.eo_name, eo_name_table.eo_pub_date
	FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name =
prod.prod_title
	WHERE prod.prod_title IS NULL;

- It's the second part that stumps me. How do I find those products from the
table `eo_name_table` that only match products in the `prod` table that have
been discontinued. In other words, I need to exclude any product match that
doesn't have any current products.

I tried this:

	SELECT eo_name_table.eo_name, eo_name_table.eo_pub_date
	FROM eo_name_table JOIN prod ON eo_name_table.eo_name =
prod.prod_title
	WHERE prod.prod_title NOT IN
	(SELECT prod.prod_title FROM eo_name_table JOIN prod ON
eo_name_table.eo_name = prod.prod_title
		WHERE prod.prod_discont = 0);

This expresses what I am trying to do, but it is not a legal query because
eo_name_table is in both the inner and outer queries.

Suggestions?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com




Thread
Find two sets of recordsJerry Schwartz15 Apr
  • Re: Find two sets of recordsBill Newton16 Apr