In addition to the other JOIN solutions provided (my preference), it can
be noted that other solutions would be to replace your value list with a
sub-select containing the first query like so:
SELECT
max(ml_id) as ml_id,
max(ml_firstname) as ml_firstname,
max(ml_lastname) as ml_lastname,
max(other) as other,
ml_email
FROM nl_mailgroups
WHERE ml_id IN (
SELECT ml_id
FROM nl_catREL
WHERE groups_id = #form.sendGROUP# AND ml_id IS NOT NULL
GROUP BY groups_id, ml_id)
GROUP BY ml_email
ORDER BY ml_email
Another option in MS SQL Server would be to declare a table variable
@tmp and populate it with the first select or use a Common Table
Expression and then join on it in the second query.
I can even think of other ways-- but I guess that's neither here nor
there. The main reason I replied was to point out a couple potential
problems with your SQL for what it's worth. (I know-- I hate being that
guy...)
Firstly, the un-paramaterized form.sendGROUP variable in your first
select is begging to be exploited via SQL injection.
Second, I don't know everything about your data structure, but you may
get unexpected results from your second select with the group by and the
max. It appears you are dealing with dupe E-mails addresses and only
which to return one record per distinct E-mail. The problem with using
max though is you might get an ID that doesn't match the name. Or
worse, a first name that doesn't match the last name. For example,
consider the following three possible records:
ml_email ms_firstname ms_lastname ml_id
[email protected] Andy Zellar 1
[email protected] Steve Andrews 2
[email protected] John Doe 3
Grouping on E-mail and taking the max names and id will return you this:
ml_email ms_firstname ms_lastname ml_id
[email protected] Steve Zellar 3
If you can guarantee that any instance of a given E-mail in your
nl_mailgroups table will always have the same name and id, then you will
be ok. Un-normalized, but ok. :)
~Brad
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319719
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
