We have to check in our transaction database to see if the product is bought. We get the data like: DB1
And this is how the transaction table looks like: DB2
And to know if the customer bought the product we want to have a sendable data extension that has the following structure:
Now I have the following query:
SELECT c.key , c.PRODUCT_1 , c.PRODUCT_2 , c.PRODUCT_3 , '1' as bought_1 FROM DB1 c WHERE EXISTS ( SELECT b.Order FROM DB2 b WHERE c.key = b.key AND c.PRODUCT_1 = b.PRODUCT )
Is there a better or more effective way to also fill in bought_2 and bought_3 instead of running three activities? Or is there a better query that can help me with this problem? Maybe AMPscript, but prefer this option as shown above, to know if they bought a product - then we skip other e-mails if they didn't buy specific products.
Thanks in advance!