Mysql – Query to find products with all the given attributes

Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.

So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)

See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.

You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.

Something like this should work :

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';

Or you can allow that user to come in from any ip adress like this:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';

(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)

I have a function that I keep on the master database for these cases. I will start by creating my function as per code below:

use master
go

create function dbo.getCities( @thecities varchar (100))
returns table
as 
return

WITH    cteTally
      AS (SELECT TOP 100
            ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
          FROM
            sys.all_columns AS AC CROSS JOIN
            sys.all_columns AS AC2  )

,RADHE2 (THECITIES)AS (
    SELECT
        LTRIM(SUBSTRING(','+@THECITIES+',',N+1,CHARINDEX(',',','+@THECITIES+',',N+1)-N-1)) AS THE_CITIES
    FROM
        cteTally C
    WHERE
        -- need the - 1 because cteTally strts with 1
        n < LEN(',' + @THECITIES + ',') AND
        SUBSTRING(',' + @THECITIES + ',', n, 1) = ','

)

SELECT * FROM RADHE2
GO 

then I will create my tables according to your question. I did not bother about indexes here, but you shoudl have a look at this, because this process is bound to be very process intensive.

SET NOCOUNT ON

DECLARE @PRODUCT TABLE (ID INT,
                    TITLE VARCHAR(100),
                    CONTENT VARCHAR(100),
                    CITY VARCHAR(100))



DECLARE @CITIES TABLE (ID INT,
                   TITLE VARCHAR(100))


INSERT INTO @PRODUCT VALUES (1,'PROD1', 'BANANAS', '1,2,4')
INSERT INTO @PRODUCT VALUES (2,'PROD2', 'APPLES', '3')
INSERT INTO @PRODUCT VALUES (3,'PROD3', 'STRAWBERRIES', '3,5')
INSERT INTO @PRODUCT VALUES (4,'PROD4', 'MANGO', '1,2,4')
INSERT INTO @PRODUCT VALUES (5,'PROD5', 'PEAR', '2,4')

INSERT INTO @CITIES VALUES (1,'CARACAS')
INSERT INTO @CITIES VALUES (2,'RIO')
INSERT INTO @CITIES VALUES (3,'TALLINN')
INSERT INTO @CITIES VALUES (4,'LIMA')
INSERT INTO @CITIES VALUES (5,'MOSCOW')

this gives me the following tables:

 select * from @PRODUCT
 select * from @CITIES

then, the final step, I use CROSS APPLY with the function, as you can see on the code below:

SELECT P.*
      ,C.TITLE
FROM @PRODUCT P 
CROSS APPLY MASTER.dbo.getCities( p.CITY) R
INNER JOIN @CITIES C ON R.THECITIES = C.ID
order by p.ID

and that gives the following result, which I believe is what you are looking for: