I-Lin provided a query solution using a left join.
I tried your query, but it didn't return the correct results.
Tried changing the "AND newsletter_subscription.Series_ID is null"
to "AND newsletter_subscription.Subscriber_ID is null", like was
included as part of the join query that I-Lin proposed, but that didn't
help.
Thanks for your help, however...
It's times like these that I am thankful for CF-Talk...
I'm going to see if I can't make at least a small donation to Mike,
every time I get a solution from this list that I couldn't solve.
Imagine...if everyone did that, Mike could make a lot more... :o)
Rick
-----Original Message-----
From: Ryan Duckworth [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 10:56 AM
To: CF-Talk
Subject: RE: How to do this join? (MySQL 4.0 can't do subqueries...)
Newsletter_Series which don't have Subscriptions for a particular
Subscriber...
<CFQUERY Name="GetSeries" Datasource="#DSN#">
SELECT newsletter_series.Series_ID, newsletter_series.Series_Title
FROM newsletter_series
WHERE newsletter_subscriptions.Subscriber_ID =
'#CurrentSubscriber.Subscriber_ID#'
AND newsletter_subscription.Series_ID is null
</CFQUERY>
I believe this query will solve your problem. Can you send your table
desctriptions?
Also, If there is no subscription, I am assuming the
newsletter_subscription.Series_ID would be null.
Ryan Duckworth
Macromedia Coldfusion Certified Professional
Uhlig Communications
10983 Granada Lane
Overland Park, KS 66207
(913) 754-4272
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 9:40 PM
To: CF-Talk
Subject: 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>
I've tried a lot of variations and read the documentation, but
can't get the
results...
Help, anyone?
Rick
Rick Faircloth
WhiteStoneMedia.com
--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.253 / Virus Database: 263.3.8 - Release Date:
6/30/2004
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
