>
> 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>
Try something like this (not tested):
SLECT newsletter_series.Series_ID, Newsletter_Subscribers.Subscriber_ID
FROM newsletter_series
LEFT JOIN newsletter_subscriptions ON newsletter_series.series_id =
Newsletter_Subscriptions.Series_ID
LEFT JOIN Newsletter_Subscribers ON Newsletter_Subscribers.Subscriber_ID =
Newsletter_Subscriptions.Subscriber_ID
WHERE newsletter_subscriptions.Subscriber_ID IS NULL
AND Newsletter_Subscribers.subscriber_name = 'mike'
AND Newsletter_Series.Series_ID = 100
--
<mack />
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
