question

Hi there,

I am trying to return a table of members minus members from another table using their unique user id (GUID). This is my main query:

SELECT 
/*FACILITY_TAG*/
ctemp.CUSTOMER_ID as CID
, ctemp.EMAIL as Email
, ctemp.BARCODE as Barcode
, ctemp.LASTNAME as LAST_NAME
, ctemp.FIRSTNAME as FIRST_NAME
, ctemp.GUID  as GUID

FROM
    (select * 
    from customers c 
    where ((( (((1)))  
    and  (( ((c.CUSTOMER_TYPE in ('GUEST')))  or  (( ((c.CUSTOMER_TYPE in ('CORPORATE','MEMBER')))  
    and  ((c.CURRENT_STATUS in ('TERMINATED'))) )) ))  
    and  (((( c.GUID in (
        select sb1.customer_guid 
        
        from schedule_bookings sb1 
        join schedule_events se1 on sb1.course_guid=se1.course_guid 
        join schedule_offerings so on sb1.OFFERING_ID = so.OFFERING_ID 
        
        where so.TITLE_ENGLISHL like '%reservation%' 
        and sb1.CANCELLED in ('0') 
        and se1.STARTDATE between '/*START_DATE*/' and '/*END_DATE*/')) 
        or ( c.GUID in (
            select sp.customer_guid 
                
            from schedule_participants sp 
            join schedule_bookings sb2 on sp.BOOKING_ID=sb2.BOOKING_ID 
            join schedule_events se2 on sb2.course_guid=se2.course_guid join schedule_offerings so on sb2.OFFERING_ID=so.OFFERING_ID 

            where 
            so.TITLE_ENGLISHL like '%reservation%'
            and sb2.CANCELLED in ('0') 
            and se2.STARTDATE between '/*START_DATE*/' and '/*END_DATE*/'))))) )) 
    and not c.customer_id in (1008,1115,264472))) as ctemp  

ORDER BY 
LAST_NAME, FIRST_NAME  limit 5000

This is the query i'm using to get a list of GUIDs for members i don't want to include

SELECT
        d.guid

        FROM
        customers d
        LEFT JOIN customers rp ON rp.CUSTOMER_ID = d.RESPONSIBLE_PARTY_ID
        LEFT JOIN (
        SELECT
        *
        FROM
        membership_tracking
        WHERE
        END_DATE_INCLUSIVE IS NULL
        GROUP BY
        CUSTOMER_ID
        ) mt ON mt.CUSTOMER_ID = d.CUSTOMER_ID
        WHERE
        (d.CUSTOMER_TYPE = 'MEMBER' or d.CUSTOMER_TYPE = 'CORPORATE')
        AND d.CURRENT_STATUS <> 'TERMINATED'

both queries work correctly on their own, but when i combine them with a simple "WHERE not in (select GUID from...)" clause no members are removed. here is the combined query that doesn't remove any members from the first query:

SELECT 
/*FACILITY_TAG*/
ctemp.CUSTOMER_ID as CID
, ctemp.EMAIL as Email
, ctemp.BARCODE as Barcode
, ctemp.LASTNAME as LAST_NAME
, ctemp.FIRSTNAME as FIRST_NAME
, ctemp.MIDDLENAME as MIDDLE_NAME
, ctemp.GUID  as GUID

FROM
    (select * 
    from customers c 
    where ((( (((1)))  
    and  (( ((c.CUSTOMER_TYPE in ('GUEST')))  or  (( ((c.CUSTOMER_TYPE in ('CORPORATE','MEMBER')))  
    and  ((c.CURRENT_STATUS in ('TERMINATED'))) )) ))  
    and  (((( c.GUID in (
        select sb1.customer_guid 
        
        from schedule_bookings sb1 
        join schedule_events se1 on sb1.course_guid=se1.course_guid 
        join schedule_offerings so on sb1.OFFERING_ID = so.OFFERING_ID 
        
        where so.TITLE_ENGLISHL like '%reservation%' 
        and sb1.CANCELLED in ('0') 
        and se1.STARTDATE between '/*START_DATE*/' and '/*END_DATE*/')) 
        or ( c.GUID in (
            select sp.customer_guid 
                
            from schedule_participants sp 
            join schedule_bookings sb2 on sp.BOOKING_ID=sb2.BOOKING_ID 
            join schedule_events se2 on sb2.course_guid=se2.course_guid join schedule_offerings so on sb2.OFFERING_ID=so.OFFERING_ID 

            where 
            so.TITLE_ENGLISHL like '%reservation%'
            and sb2.CANCELLED in ('0') 
            and se2.STARTDATE between '/*START_DATE*/' and '/*END_DATE*/'))))) )) 
    and not c.customer_id in (1008,1115,264472))) as ctemp  
WHERE
    ctemp.GUID not in(
        SELECT
        d.guid

        FROM
        customers d
        LEFT JOIN customers rp ON rp.CUSTOMER_ID = d.RESPONSIBLE_PARTY_ID
        LEFT JOIN (
        SELECT
        *
        FROM
        membership_tracking
        WHERE
        END_DATE_INCLUSIVE IS NULL
        GROUP BY
        CUSTOMER_ID
        ) mt ON mt.CUSTOMER_ID = d.CUSTOMER_ID
        WHERE
        (d.CUSTOMER_TYPE = 'MEMBER' or d.CUSTOMER_TYPE = 'CORPORATE')
        AND d.CURRENT_STATUS <> 'TERMINATED'
        )
ORDER BY 
LAST_NAME, FIRST_NAME  limit 5000

when i change the where clause to just a user id in quotes like so:

WHERE
   ctemp.GUID not in('4166514b-48e3-4ee4-9b43-26b0bdb1ab2a')

the query works and the member is removed.

i've tried adding apostrophes in the second query multiple ways:

''+d.GUID+''

concat(''',d.GUID,''')

etc.

but none of them result in the members being removed.

sorry for the long post. thank you for reading this far and please let me know if you need any additional information.