On Sat, Feb 24, 2001 at 08:56:03AM +0100, Fred van Engen wrote:
> 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.
>
You may also try this:
SELECT iSubId
FROM Issue
GROUP BY iSubId
HAVING MAX(iIssue) = 8
I don't use HAVING usually, so you might want to check this.
It does assume that there will be no gaps in issues for a
subscriber (i.e. a subscriber with issues 5,6,7,8,20,21 won't
be found with this).
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
