QofQ?

Ok for some odd reason this seems to work, but not giving me the results I
am looking for:

SELECT sendlist.email, sendlist.name
FROM sendlist, Optouttable
WHERE sendlist.email = Optouttable.email AND Optouttable.email Is Null

I need this:
SELECT sendlist.email, sendlist.name
FROM sendlist LEFT JOIN Optouttable ON sendlist.email = Optouttable.email
WHERE Optouttable.email Is Null

How can I do this with your (or another) method?

Ummm...That last bit threw me off..

> Ahh, PK = primary key, FK = foreign key

Yes. But your query below is being a bit literal. Mine was example code,
you weren't supposed to copy it verbatim. I mean "[use primary key here]",
and "[use foreign key here]".

> <cfquery name="maillist" dbtype="query">
> SELECT email, name
> FROM emailList,Optout
> WHERE email.PK = email.FK AND email.FK Is Null

WHERE E.email = O.email
AND O.email Is Null

Now, if you NEED to use an outer join, you're perhaps best not attempting
this approach. Whilst it's POSSIBLE to effect an outer-join-esque record
set with QoQ, it's more hassle than it's worth.

1) Stick with the two queries you've got.
2) Extract the email column from optout with valueList().
3) Loop over SendList, so you definitely hit all records.
4) For each record of SendList, do a listFindNoCase() on the list extracted
in (2).
5) If there's a match, then use the index of the match to access the
appropriate row in optout, to extract the data from optout that corresponds
to the current record of SendList.

This will only work if there are no null values in optout.email, due to
limitations of CF's ability to handle null-data in lists.

Make sense?

--
Adam

Ahh, PK = primary key, FK = foreign key

<cfquery name="maillist" dbtype="query">
SELECT email, name
FROM emailList,Optout
WHERE email.PK = email.FK AND email.FK Is Null
</cfquery>

So how close is the query above to outputting all the names from the first
query (emailList) that do not appear in Optout?

I am a little confused on the tables I need to select. Can I do this?

<cfquery name="maillist" dbtype="query">
SELECT email, name
FROM emailList,Optout
WHERE email.PK = email.FK AND email.FK Is Null
</cfquery>

Sorry, a wee bit lost on the PK and FK

"Adam Cameron" <adam_junk@hotmail.com> wrote in message
news:1fyie2rt5fc35$.e0wcnm0j5syj.dlg@40tude.net...
> You seem to be on the right track, but...
>
>> <cfquery name="emailList" datasource="**">
>
> Drop the datasource attribute and use dbtype="query" instead.
>
>> >> FROM SendList E LEFT JOIN optout O
>
> You cannot use JOIN syntax in a QoQ. You can only use the notation:
>
>
>
> Are you sure you want an OUTER join for your requirement, anyhow?
>
> --
> Adam

Then do what Adam told you to do.

I need to compare the imported (emailList) against the database (Optout) and
produce a list of what is not the same. The emailList is an imported list of
email addresses for a bulk mailer. The Optout (as you can imagine) is people
who opt'd out of our mailings. I need to then do a bulk mailing with the
results of whats left.

For example:
emailList Optout
joe@me.com joe@me.com
sue@sue.com sue@sue.com
mike@mike.com john@john.com

The results would only be mike@mike.com

you need two Q of Q's

QofQ1, get all the records from output where email is not null

QofQ2, select stuff from sendlist where the email is not in the valuelist from QofQ1.

You seem to be on the right track, but...

> <cfquery name="emailList" datasource="**">

Drop the datasource attribute and use dbtype="query" instead.

> SELECT E.email, E.name
> FROM SendList E LEFT JOIN optout O

You cannot use JOIN syntax in a QoQ. You can only use the notation:

FROM table1,table2
WHERE table1.PK = table2.FK

Are you sure you want an OUTER join for your requirement, anyhow?

--
Adam