Yes, MySQL does now support subqueries, but not the production version.
I won't be going to 4.1 until it's in production, so I'm still using 4.0.x,
which
doesn't support subqueries.
And, the first thing I did was to consult the MySQL documentation
on the precise pages your refer to, but I couldn't make my query work
from the examples given...
Your query below worked...I tried almost the exact same version, but I don't
think I tried the WHERE clause that you used...and it's the last line that
gives trouble logically...
"WHERE newsletter_subscriptions.Subscriber_ID IS NULL"
I've got to just sit and ponder the logic of the that statement in relation
to the join. I use joins very seldom, so they give me fits every time I
have
to use one...
But that's enough rambling!
Thanks for the solution!
Rick
-----Original Message-----
From: I-Lin Kuo [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 9:20 AM
To: CF-Talk
Subject: Re: How to do this join? (MySQL 4.0 can't do subqueries...)
MySql now supports subqueries:
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]
