AWS - Locale/Cats SQL Query Problem

[oohoohoohooh]

Hi, I'm trying to set up my own amazon web service. My problem comes from different stores only available to certain countries. Please check

http://www.amazon.com/gp/aws/sdk/ma...v=2005-03-23&p=ApiReference/SearchIndexValues

for information on which stores are available to which country.

I want to setup my store so that I can easily update these categories on further releases of the amazon webservice as it is forever changing. I have therefore set up a few tables:

amazon_locales:
- amazon_local_id
- amazon_local_name
- code

amazon_cats:
- amazon_cat_id
- amazon_cat_name

amazon_cat_lines:
- amazon_local_id
- amazon_cat_id

The amazon_locales table would contain all the available locals such as US, UK, DE etc and the amazon_cats table would contain all the available stores such as Music, DVD, VHS etc. The amazon_cat_lines table provides the link between the locale and the cat to see if it is available for any given locale (if a record exits it does if not then it is not).

Hope all is clear so far. I have now setup an additional table which is the categories table which provides the categories the site will use. This allows me to setup categories and sub categories linking to the available amazon_cats (stores). Here is its structure:

cats:
- cat_id
- subcat_id (if 0 then it is not a subcat, if it contains a value then it belongs to that cat)
- cat_name
- amazon_cat_id (this specified the store lookup data from - if this value is 0 then no store selected)

OK now here's my problem. I need to select all the cats (not subcats) to display for a given local (in this example we will use amazon_local_id=1).

I tried:

SELECT c.* FROM cats c LEFT OUTER JOIN amazon_cat_lines acl ON c.amazon_cat_id=acl.amazon_cat_id && acl.amazon_local_id=1 WHERE c.subcat_id=0

I'm sure it is quite close but since I had to use an outer join since the amazon_cat_id value can be 0 it will still return every category even though no record exists in the amazon_cat_lines table to show that it is available to that particular locale.

I know this is alot to take in but I'm sure it has a simple solution and would appreciate it if someone could help. Thanks

 

[amrita418]

Try this:

SELECT     c.* 
FROM       cats c LEFT OUTER JOIN amazon_cat_lines acl 
           ON c.amazon_cat_id = acl.amazon_cat_id   
WHERE      c.subcat_id = 0
           and acl.amazon_local_id = 1

Regards,
AA

 

[oohoohoohooh]

Hi I tried:

SELECT    c.*
FROM      cats c LEFT OUTER JOIN amazon_cat_lines acl
          ON c.amazon_cat_id=acl.amazon_cat_id
WHERE     c.subcat_id='0' AND acl.amazon_locale_id='1'

As you said but if the amazon_cat_id is zero it did not return the value otherwise it worked fine.

ie the following:

cat_id | subcat_id | cat_name          | amazon_cat_id
   1          0     Books, Music, DVD'     0
   2          0     Electronics            9
   3          0     Kids & Baby        4 
   4          0     Home & Garden      3
   5          1     Books                  1
   6          1     DVD                    8 
   7          1     Music                  19 
   8          1     VHS                    33

Returned:

Books, Music, DVD
Electronics
Kids & Baby

by looping through the array and outputting the cat_name field.

Note there is no record in the amazon_cat_lines table where the amazon_local_id=1 and the amazon_cat_id=3 (as expected).

 

[oohoohoohooh]

Sorry I should have put returned:

Electronics
Kids & Baby

 

[amrita418]

I think I am not following you correctly, Can you post some sample data (both cats and amazon_cat_lines tables) and expected output?

Regards,
AA

 

[oohoohoohooh]

Hi, after spending all day doing various combinations and changes I realized the solution was quite simple. In case anyone is interested here's what I got:

SELECT c.* FROM cats c LEFT OUTER JOIN " . amazon_cat_lines acl ON c.amazon_cat_id=acl.amazon_cat_id WHERE c.status='1' AND c.subcat_id='0' AND (c.amazon_cat_id='0' OR acl.amazon_locale_id='1'