I am having trouble working out how to pull up shipping options from a table based on two parameters, countryID and productCode. I have the following 2 tables:
What I want to do is feed it say, 3 product codes and it will return only the countries/shipping options that are available for ALL products its been fed - I've thied this so far:
SELECT DISTINCT SYSTEM_countries.countryName, SYSTEM_countries.countryID FROM ECOMMERCE_shipping INNER JOIN SYSTEM_countries ON ECOMMERCE_shipping.countryID = SYSTEM_countries.countryID WHERE ECOMMERCE_shipping.productCode = 'bvical2007' OR ECOMMERCE_shipping.productCode = 'BVIFLAGLG'
But all this does is list the shipping options available for both or either of the products. I cannot change the last part of the query to "AND" obviously (ECOMMERCE_shipping.productCode = 'bvical2007' AND ECOMMERCE_shipping.productCode = 'BVIFLAGLG') as this is an impossible statement.
I know I'm going about this the wrong way but I've just hit a wall!
If anyone has any pointers I'd really appreciate it!