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
