Inverting SQL query. What to use instead of EXCEPT in SFMC?

Question

I have a pretty simple issue but my several attempts don't return desirable results. The following query updates the field of DE (noImax) with "true" for those who meet specified criteria. What I need is to apply this "true value" for all who does not meet it (remaining audience, not equality will not work in this case). EXCEPT would work for it but, apparently, it's not supported in SFMC. I tried to use NOT EXISTS but it did not work as well (maybe, it was syntax issue)

SELECT clientIdentifier, 
dateOfRegistration, phoneNumber, locale, phoneAppr, emailAddress, emailAppr, 
sourceOfReg, dateOfBirthday, gender, 
memberClub, sendEmail, sendSms, sendViber, sendPush, kinomama, 
originalDubbing, forKids, documentaries, broadcast, 
vr, newsDelivery, personalDelivery, surveyAfterMovie, clientFirstName, 
clientLastName, bonuses, 'true' AS noImax, 
no4dx, noRelux, noVR, noVIP, noKinomarket, noBuySite, noBuyMA, lastBuyCity, 
daysSinceRegistration, countVisitsMonth, 
sumTicketsPrice, sumBarPrice, avgBillTikets, avgBillBar, daytimeWeekdays, 
priceSensitivity, riskOutflow, rfm

FROM Partners_test
LEFT JOIN TransactionTickets ON Partners_test.clientIdentifier = 
TransactionTickets.clientId
WHERE TransactionTickets.technology = 'IMAX 3D' OR 
TransactionTickets.technology = 'IMAX 2D'

Thank you for your time!

Answer 1

If I correctly understand, then really all you need to do is change the WHERE statements from = (equal to) to != (not equal to) while also changing OR to AND.

e.g.

WHERE TransactionTickets.technology != 'IMAX 3D' AND 
TransactionTickets.technology != 'IMAX 2D'

You could also try NOT IN:

WHERE TransactionTickets.technology NOT IN ('IMAX 3D','IMAX 2D')

I might also add in some NULL handling as well:

OR TransactionTickets.technology IS NULL

Which would make the following:

SELECT clientIdentifier, 
dateOfRegistration, phoneNumber, locale, phoneAppr, emailAddress, emailAppr, 
sourceOfReg, dateOfBirthday, gender, 
memberClub, sendEmail, sendSms, sendViber, sendPush, kinomama, 
originalDubbing, forKids, documentaries, broadcast, 
vr, newsDelivery, personalDelivery, surveyAfterMovie, clientFirstName, 
clientLastName, bonuses, 'true' AS noImax, 
no4dx, noRelux, noVR, noVIP, noKinomarket, noBuySite, noBuyMA, lastBuyCity, 
daysSinceRegistration, countVisitsMonth, 
sumTicketsPrice, sumBarPrice, avgBillTikets, avgBillBar, daytimeWeekdays, 
priceSensitivity, riskOutflow, rfm

FROM Partners_test
LEFT JOIN TransactionTickets ON Partners_test.clientIdentifier = 
TransactionTickets.clientId
WHERE TransactionTickets.technology NOT IN'(IMAX 3D','IMAX 2D')
OR TransactionTickets.technology IS NULL

OR If you need true/false you can also set a case statement:

SELECT ..... (your other select fields here)
CASE
     WHEN TransactionTickets.technology IN ('IMAX 3D','IMAX 2D') THEN 'false'
     ELSE 'true'
END
............. (remainder of your select fields here)

FROM Partners_test
LEFT JOIN TransactionTickets ON Partners_test.clientIdentifier = 
TransactionTickets.clientId