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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
