On 29 January 2013 22:42, O. Olson <olson_...@yahoo.it> wrote:
[...]
> SQL Database Schema:
>
> Table: Prod_Table
> Column 1: SKU <- ID/Primary Key
> Column 2: Title
>
> Table: Cat_Table
> Column 1: SKU <- Foreign Key
> Column 2: CategoryLevel
> Column 3: CategoryName
>
> Where CategoryLevel is 1, I would like to save the value to Category1 field,
> where CategoryLevel is 2, I would like to save this to the Category2 field
> etc.
[...]
It is not very clear from your description, nor from your example,
what you want saved to the Category1, Category2,... fields, and
how you expect your user searches to function. You seem to imply
that the categories are hierarchical, but there is no relationship in
the database to define this hierarchy.
For a given product SKU, do you want the multi-valued Category1
field to contain all CategoryName values from Cat_Table that have
CategoryLevel = 1 and SKU matching the product SKU, and so on
for the other categories? If so, this should do it:
<entity name="Product" query="SELECT SKU, Title FROM PROD_TABLE">
<field column="SKU" name="SKU" />
<field column="Title" name="Title" />
<entity name="Cat1" query="SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1">
<field column="CategoryName" name="Category1" />
</entity>
<entity name="Cat2" query="SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1">
<field column="CategoryName" name="Category2" />
</entity>
<entity name="Cat3" query="SELECT CategoryName from CAT_TABLE where
SKU='${Product.SKU}' AND CategoryLevel=1">
<field column="CategoryName" name="Category3" />
</entity>
</entity>
Regards,
Gora
