Merlin Merlin - 7 months ago 35
SQL Question

SQL Exclude via Subquery

I have a situation where I need to pull member records back from the 'MEMBER' table. I need to retrieve last name, first name, relationship, entrydate, member ID, email and site ID. The criteria are a relationship of 'P', a siteid of '111' and a non-blank email address AND I need to exclude two subsets. Subset A has all of the criteria above plus a User Defined Field with a user defined field id of 26, a user defined field value of 'No' and an entry date of more than 6 days ago. Subset B has the same main criteria but an entry date of more than 2 weeks ago and a User Defined Field of 25 and a used defined field value of 25. The User Defined fields and values are stored in a separate table (MEMBERUDFS) from the Member data. The tricky part is if a user has not set the value of a user defined field there isn't an entry on the MEMBERUDFS table. The two table are linked with the key of memid.

This is what I have so far and it does not exclude group A and I have not attempted to add logic to exclude group B. Any help would be greatly appreciated!!! :

SELECT MEMBERS.scancode,
MEMBERS.memid,
MEMBERS.fname,
MEMBERS.lname,
MEMBERS.relationship,
MEMBERS.STATUS,
MEMBERS.email,
MEMBERS.entrydate,
SITES.sitename
FROM MEMBERS
INNER JOIN SITES ON MEMBERS.siteid = SITES.siteid
LEFT OUTER JOIN MEMBERUDFS ON MEMBERS.memid = MEMBERUDFS.memid
INNER JOIN MEMBERUDFSETUPS ON MEMBERUDFS.udfid = MEMBERUDFSETUPS.udfid
WHERE (MEMBERS.relationship = 'P')
AND (MEMBERS.email <> '')
AND (
MEMBERS.memid NOT IN (
SELECT MEMBERS_1.memid
FROM MEMBERS AS MEMBERS_1
INNER JOIN SITES AS SITES_1 ON MEMBERS_1.siteid = SITES_1.siteid
LEFT OUTER JOIN MEMBERUDFS AS MEMBERUDFS_1 ON MEMBERS_1.memid = MEMBERUDFS_1.memid
INNER JOIN MEMBERUDFSETUPS AS MEMBERUDFSETUPS_1 ON MEMBERUDFS_1.udfid = MEMBERUDFSETUPS_1.udfid
WHERE (MEMBERS_1.relationship = 'P')
AND (MEMBERS_1.email <> '')
AND (MEMBERUDFS_1.udfid = '26')
AND (MEMBERUDFS_1.udfvalue = 'No')
AND (MEMBERS_1.entrydate < DATEADD(DAY, 6, GETDATE()))
AND (MEMBERS_1.siteid = @rvSite)
)
)
AND (MEMBERS.siteid = @rvSite)

Answer

Seems like you should be able to use NOT EXISTS here and remove the UDF tables from your main query, since you're not selecting anything from them.

SELECT MEMBERS.scancode,
    MEMBERS.memid,
    MEMBERS.fname,
    MEMBERS.lname,
    MEMBERS.relationship,
    MEMBERS.STATUS,
    MEMBERS.email,
    MEMBERS.entrydate,
    SITES.sitename
FROM MEMBERS
INNER JOIN SITES ON MEMBERS.siteid = SITES.siteid
WHERE (MEMBERS.relationship = 'P')
    AND (MEMBERS.email <> '')
    AND (MEMBERS.siteid = @rvSite)
    AND NOT EXISTS (
        SELECT  1 
        FROM    MEMBERUDFS AS MEMBERUDFS_1 ON MEMBERS_1.memid = MEMBERUDFS_1.memid
                INNER JOIN MEMBERUDFSETUPS AS MEMBERUDFSETUPS_1 ON MEMBERUDFS_1.udfid = MEMBERUDFSETUPS_1.udfid
        WHERE   MEMBERS.memid = MEMBERUDFS_1.memid 
                AND (MEMBERUDFS_1.udfid = '26')
                AND (MEMBERUDFS_1.udfvalue = 'No')
                AND (MEMBERS.entrydate < DATEADD(DAY, 6, GETDATE()))
    )
Comments