How to map columns to get correct data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to map columns to get correct data?

Author

Topic

memory721
Starting Member

8 Posts

Posted - 2010-06-13 : 14:05:52

Hello,
I have a query that is as follows but I need to add another table to pull the correct definitions. rules.rule_name needs to be defined by flex_labels.value and rule_builders.rule_builder_name needs to be defined by flex_labels.value.

This is what I have, but this query doesn't bring in the info from the flex_labes table. I don't know how to combine the 2 parts of the query.

Select category_name
,rule_name
, rule_builders_id
, rule_builders_name
from rule_categories inner join rules on rule_categories.id=rules.rule_category_id
inner join rule_builders on rule_builders.id = rules.rule_builder_id
WHERE category_name = 'Archive'

The problem is that in the rules table where I have the column named rules_name, that column only houses the internal definition of the rule. To get the user friendly description of the rule_name column, I have to somehow use the flex_labels table and use the column named 'value'. The same goes for the rule_builders_name column from the rule_builders table. I have to use the flex_labels table column 'value' to get the correct description. I just have no idea how to map the rules.rules_name to flex_labels.value and the rule_builders_name to flex_labels.value.
Can somebody please help me?
Thank you.

SQL user

SQL user

Sachin.Nand

2937 Posts

Posted - 2010-06-14 : 03:40:22

Which column maps the tables flex_labels and rule_categories ?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

memory721
Starting Member

8 Posts

Posted - 2010-06-14 : 09:40:53

The rules table has a column named rule_name that maps to the flex_label table and the value column.

The rule_builders table has a column named rule_builder_name that maps to the flex_lable table and the value column.

Thank you.

SQL user

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 10:02:47

How are both the tables related? Pls. post the table structure for the rules, rule_builder and flex_value tables. Also some sample data from the tables.

memory721
Starting Member

8 Posts

Posted - 2010-06-14 : 11:57:46

The tables are related using hierarchies.id.
Table structure for Rules: Example Data:
Rules.id 11111111
Rules.hierarchy_id 7777777
Rules.rule_name Purge IP
Rules.rule_builder_id 2222222

Table structure for rule_builder table: Sample Data:
Rule_builder.id 2222222
Rule_builder.rule_builder_name Neg_Pos_Purge Rule

Table structure for flex_labels: Sample data:
flex_labels.hierarchy_id 7777777
flex_labels.value Neg/Pos Purge Rule

I have an inner join between Rules and flex_labels, but it pulls all the different descriptions.

SQL user

memory721
Starting Member

8 Posts

Posted - 2010-06-14 : 12:22:18

I just found a little bit of documentation that states the following:

: flexlables need to be uniquely identified by more that one column, not just hierarchy. so, using again the "Data Acquire" example, you should add additional criteria, like flex_labels.TABLENAME = 'rule_categories', FIELDNAME = 'categoryName', FIELDVALUE = 'Data Acquire', in addition to HIERARCHY_ID.

How do I incorporate that logic into my query which looks like this:
SELECT
RULES."RULE_NAME" AS RULES_RULE_NAME,
RULE_CATEGORIES."CATEGORY_NAME" AS RULE_CATEGORIES_CATEGORY_NAME,
RULE_BUILDERS."RULE_BUILDER_NAME" AS RULE_BUILDERS_RULE_BUILDER_NAM,
RULES."ID" AS RULES_ID
FROM
"CORE"."RULE_CATEGORIES" RULE_CATEGORIES INNER JOIN "CORE"."RULES" RULES ON RULE_CATEGORIES."ID" = RULES."RULE_CATEGORY_ID"
INNER JOIN "CORE"."RULE_BUILDERS" RULE_BUILDERS ON RULES."RULE_BUILDER_ID" = RULE_BUILDERS."ID"
INNER JOIN "CORE"."HIERARCHIES" HIERARCHIES ON RULES."HIERARCHY_ID" = HIERARCHIES."ID"
AND HIERARCHIES."ID" = RULE_CATEGORIES."HIERARCHY_ID"
WHERE
hierarchies.id = '5237260000000000082'
And category_name = 'Archive'

SQL user

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources