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