On Fri, Feb 23, 2001 at 06:05:53PM -0600, Don Hosek wrote:
> 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
>
> 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
>
Your LEFT JOIN would generate rows for any combination of iIssue
for an iSubId and then SELECT only those where iSubId is NULL,
which I guess it never will be.
You might want to check the manual for the exact workings of a
LEFT JOIN.
What you would need is this:
SELECT i1.iSubId
FROM Issue AS i1
LEFT JOIN Issue AS i2
ON i1.iSubId=i2.iSubId AND i2.iIssue = i1.iIssue + 1
WHERE i1.iIssue=8 AND i2.iIssue IS NULL
The LEFT JOIN then tries to find for each record in i1, one or
more records in i2 with the same iSubId as the record in i1 and
an iIssue one higher than the record in i1. If no such record
exists, it will generate a row anyway, but this will have NULL
values for all fields in i2.
So the WHERE will check for a NULL field in i2 and for the iIssue
you want and return only those rows.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
