http://dev.mysql.com/doc/mysql/en/Subqueries.html
To see how to rewrite some subquery SQL as joins, go
to
http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html
SELECT newsletter_series.Series_ID,
newsletter_series.Series_Title
FROM newsletter_series
LEFT JOIN newsletter_subscriptions
ON newsletter_series.Series_ID =
newsletter_subscription.Series_ID
AND newsletter_subscriptions.Subscriber_ID =
'#CurrentSubscriber.Subscriber_ID#'
WHERE newsletter_subscriptions.Subscriber_ID IS NULL
--- Neculai Macarie <[EMAIL PROTECTED]> wrote:
> > Hi, all.
> >
> > I've tried for hours to figure this out, but can't
> get it...
> >
> > 3 tables...
> >
> > Newsletter_Series, Newsletter_Subscribers,
> Newsletter_Subscriptions
> >
> > I need to run a query to get all Newsletter_Series
> > which don't have Subscriptions for a particular
> Subscriber...
> >
> > Primary Key Relationships:
> >
> > - Newsletter_Series.Series_ID /
> Newsletter_Subscriptions.Series_ID
> > - Newsletter_Subscriptions.Subscriber_ID /
> > Newsletter_Subscribers.Subscriber_ID
> >
> >
> > Can't do subqueries (aaargh!), so I'm left with a
> join.
> >
> > Something like,
> >
> > <CFQUERY Name="GetSeries" Datasource="#DSN#">
> >
> > Select newsletter_series.Series_ID,
> > newsletter_series.Series_Title,
> > from newsletter_series
> >
> > left join
> >
> > newsletter_subscriptions,
> newsletter_subscribers
> > on (newsletter_subscriptions.Subscriber_ID <>
> > '#CurrentSubscriber.Subscriber_ID#'
> > and newsletter_subscription.Series_ID <>
> > newsletter_series.Series_ID)
> > where newsletter_series.Series_ID is null
> >
> > </CFQUERY>
>
=====
I-Lin Kuo
Macromedia CF5 Advanced Developer
Sun Certified Java 2 Programmer
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
