From: Date: April 16 2008 12:22am
Subject: Re: Find two sets of records
List-Archive: http://lists.mysql.com/mysql/212301
Message-Id: <48052AA1.1050306@networkmerchants.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Try using the sub query as a derived table ala:

SELECT eo_name_table.eo_name, eo_name_table.eo_pub_date
	FROM eo_name_table LEFT JOIN (SELECT prod.prod_title FROM prod WHERE prod.prod_discont = 0) fake_prod on eo_name_table.eo_name = fake_prod.prod_title 
	WHERE prod.prod_title IS NULL

I'm not sure if the logic is correct, although it seems to be the same as your attempt you want eo_name and eo_pub_date from where their are not any matches with rows that have prod_discount =0.


Hope that helps.


 




Try a self join :

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



Jerry Schwartz wrote:
> 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
>
>
>
>
>
>