Haven't been able to get it to work, yet...but I'm still trying.
One problem, I believe, is the last line of your SQL:
"and Newsletter_Series.Series_ID = 100"
I know that 100 is not to be taken literally,
but the problem is in defining the Series_ID at all.
The query is supposed to identify *all* Series_ID's, not one specific
Series_ID.
With or without that line, however, I have haven't been able to get the left
joins to work.
I'll keep trying...
Rick
-----Original Message-----
From: Neculai Macarie [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 6:21 AM
To: CF-Talk
Subject: Re: How to do this join? (MySQL 4.0 can't do subqueries...)
> 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>
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]
