----- Original Message ----- From: "Don Hosek" <do...@dream-in-color.net> To: <mys...@lists.mysql.com> Sent: Friday, February 23, 2001 6:05 PM Subject: Help me deal with this subquery removal

This'd be a piece of cake with sub queries: What I have is a table with two relevant fields: iIssue and iSubId

iSubId represents a magazine subscriber iIssue represents any issues that person has/had coming

So, for example, if someone is subscribed for 4 issues and has subscriber ID 47, they will generate four rows in the table:

47 11 47 12 47 13 47 14

I want to be able to determine whether someone is at the end of their subscription. The query should be give me all the subscriber IDs which have a record for iIssue=N but not for iIssue=N+1

as a sub-query, I would write:

SELECT iSubID FROM Issues WHERE iIssue=N AND iSubID NOT IN ( SELECT iSubID FROM Issues WHERE iIssue=N+1 )

My first attempt based on the example from the documentation:

SELECT i1.iSubId FROM Issue AS i1 LEFT JOIN Issue AS i2 ON i1.iSubId=i2.iSubId WHERE i2.iSubId IS NULL AND i2.iIssue=9 AND i1.iIssue=8

Returned 0 rows (if it were correct it would have returned at least one row).

Any ideas?

-dh