SQL Select causing multiples...

[bigbrain28]

I hate to bother with this, as I have had this issue before and solved it - But
I cannot remember what I did! I have t tables, a member list (with ID) and a
email table (with ID keyed to the member table). I am using the email table as
a boolean to allow emails to those members. If their ID exists in the email
table, its a yes... So in my control panel I have 2 queries, one for the
opt-ins:
<!---Get OPT-INS--->
<cfquery datasource="#DSN#" name="optin">
SELECT distinct
#SESSION.ID.area#members.ID,#SESSION.ID.area#EMAIL .ID,#SESSION.ID.area#members.b
usName
FROM #SESSION.ID.area#members, #SESSION.ID.area#EMAIL
WHERE #session.ID.area#members.ID=#session.ID.area#EMAIL .ID
ORDER BY #SESSION.ID.area#members.busName
</cfquery>

which works fine, and one for OPT-OUTs;

<!---Get OPT-OUTS--->
<cfquery datasource="#DSN#" name="optout">
SELECT DISTINCT
#SESSION.ID.area#members.ID,#SESSION.ID.area#EMAIL .ID,#SESSION.ID.area#members.b
usName
FROM #SESSION.ID.area#members, #SESSION.ID.area#EMAIL
WHERE #SESSION.ID.area#members.ID<>#SESSION.ID.area#EMAI L.ID
ORDER BY #SESSION.ID.area#members.busName
</cfquery>

Which DOES NOT work as planned...

What the OPT-OUT query does is check the email table for a match against EACH
ID, so if I have 10 OPT-IN's in the email table, the OPT-OUT result for EACH
mamber is repeated 10 times. Basically I need the OPT-OUT query to simply show
a list of members who's ID nuber does NOT appear in the email table.

[mxstu]

>simply show a list of members who's ID nuber does NOT appear in the email table

One method is to use a subquery

SELECT memberID FROM tableOne WHERE memberID NOT IN (SELECT memberID from
tableTwo)

... or a left join ...

SELECT t1.memberID
FROM tableOne t1 LEFT JOIN tableTwo t2 ON t1.memberID = t2.memberID
WHERE t2.memberID IS NULL

* not tested

[bigbrain28]

Awesome! That first method worked perfectly. Thanks so much for the quick response, you are a lifesaver!