question

gus_the_climber avatar image
gus_the_climber asked

trying to get a table minues values from another table

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.

sqlmysql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swarnava2014 avatar image
swarnava2014 answered

Please try : '''+d.GUID+'''

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gus_the_climber avatar image
gus_the_climber answered

@swarnava2014 thanks for the suggestion. unfortunately that didn't work either.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swarnava2014 avatar image
swarnava2014 answered

@gus_the_climber - Please try in a different approach. Put the first part of the query in a cte and then apply the where clause on the cte

;with cte_t1 as ( 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 ) select cte_t1.CUSTOMER_ID as CID , cte_t1.EMAIL as Email , cte_t1.BARCODE as Barcode , cte_t1.LASTNAME as LAST_NAME , cte_t1.FIRSTNAME as FIRST_NAME , cte_t1.MIDDLENAME as MIDDLE_NAME , cte_t1.GUID as GUID WHERE cte_t1.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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.