SQL Query Syntax

[Senor Fett]

Hi all,

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:

ECOMMERCE_shipping
shippingID
countryID
productCode
cost1to2

SYSTEM_countries
countryID
countryName

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!

Cheers

Bob :eek:

[tgreer]

select shippingID 
from ECOMMERCE_shipping  
where countryID = 'X' 
and (productCode = 'A' or productCode = 'B' or productCode = 'C')

Something like that should do the trick.

[Senor Fett]

Hi Tgreer,

Thanks for the very quick reply - I'm afraid I made a slight error in my original post which I've amended now (I'm not feeding it a country ID), I'm feeding it just product codes but an OR statement just pulls up shipping options available for all the products but an AND statement wont work as it is (as I'd be asking the DB for a product with a productCode of "BLAH1" AND a productCode of "BLAH2")

I'm sure I'm doing something fundamentally stupid (not a rare occurance) but I can't see it.

Thanks again however

Cheers

Bob

[tgreer]

Ok, then I'm very unclear what you're trying to do. You want all shipping codes for any of 3 different product codes?

select shippingID from ECOMMERCE_shipping 
where (productID = 'A' OR productID = 'B" OR productID = 'C')

[Senor Fett]

Hi Thomas,

Thanks again for getting back to me - ok, here's a non-SQL description of what I'm trying to achieve:

Each product has a limited number of destination countries it can be sent to.

As a customer adds various products to their shopping cart, the number of destination countries should lessen because my lovely new SQL statement should only be showing the destination countries available for all products.

The problem is an OR query is obviously just pulling destination countries for any/all products and an AND statement doesn't seem workable.

Thanks

Bob

[tgreer]

If you want all CountryIDs for ALL of a set of ProductCodes, then an "AND" query is definitely what you want:

seleft countryID from ECOMMERCE_shipping
where
ProductCode = 'A' AND
ProductCode = 'B' AND
ProductCode = 'C'

[Senor Fett]

Hi Thomas,

Thats what I thought but its an impossible statement; as the entry cannot have a productCode that is 'A', 'B' AND 'C' - this is why I figure I'm completely missing something.

Cheers

Bob