query where one value is not present in 2 tables

change this:
members.memberID <> tblMemberEmailHistory.MemberID
to this:
members.memberID NOT IN (SELECT DISTINCT MemberID FROM tblMemberEmailHistory)

Azadi

aneeshattingal:
Or Logic..they are all bit types either true or false. no nulls (sql server)
each member can have a true value of each field depending. this email is sent out via a form and the person can check who he want to send it to. It is always sent to the InternalManager, never to the CandidatureMember and depending on what the person chooses sometimes to the ClientMemberand sometimes to the PublicMember

aneeshattingal is correct about  your logic being a problem.  I mentioned this on another thread as well.  The AND / OR operators should be grouped by parenthesis to enforce the proper order of evaluation, so the database will return the records you want.  Otherwise, your'e just leaving the evaluation it up to the database, which may or may not return the desired results.


For example, this grouping would find only records where

WHERE
---      _all_ of these conditions are true
(
       members.active = 1 AND
       tblMemberEmailHistory.MemberEmailID = 1 AND
       members.InternalManager = 1 AND
       members.CandidatureMember = 0
)

--  OR .. where _either_ of these conditions is true
OR  members.ClientMember = NULL
OR  members.PublicMember = 1

ok I understand it's an AND OR and grouping issue. Now I just need a little help grouping it properly. Let's have a look at this where clauseand what I'm trying to get out of it:

--- the first part of the where clause is to restrict the member list to members who are not yet in tblMemberEmailHistory...at least not for the MemberEmailID = 1 AND they must be active members. I put brackets around this part
WHERE     ((members.memberID <> tblMemberEmailHistory.MemberID)
AND (members.active = 1)
AND (tblMemberEmailHistory.MemberEmailID = 1))

--- in this next part at least one of these conditions must be met to be in the list. In this example I have checked or given both InternalManager  and PublicMember valoues of 1 which should give us the members who are either InternalManagers  or PublicMembers. the mail should not go out to members who are CandidatureMembers  or PublicMember s unless of course they are also eitherInternalManager  or PublicMembers

AND
((members.InternalManager = 1)
or (members.CandidatureMember = Null)
or  (members.ClientMember = NULL)
or  (members.PublicMember = 1))