Queries on Multi-Index Fields - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Queries on Multi-Index Fields

Author

Topic

Armysniper89
Starting Member

6 Posts

Posted - 2011-10-16 : 10:03:17

I am using a CMS called Kentico where they create custom tables in SQL 2008. They offer the ability for users of the CMS to create records in the CMS. In fields where there are multiple selections, you can use a lookup table and the CMS stores the value as a piped string of indexes...(such as "1|7|10"). I am trying to figure out how to query this field.

For example, I have a custom Events table and one of my fields allows the creator of the event record the ability to choose multiple age groups that may be interested in the event such as toddlers, adults, teens, etc. So an event might have in its age group field multiple age groups that an event matches to. So it might have a value of "1|7|10" stored as a string, not a single integer.

I have to create a search filter. When a user who is looking for events enters in lets say "toddler" as an age group which is index 1, I want to search all records that have "1" for a value of the age groups field...but an event might have "1|7|10" as a value for "age group". How can I query that if I have single integer index field to search for...yet the index is stored in a field that contains a string of "index" values?

Also, if I want to inner join on that age group field with the lookup table...how can I so I can display in an HTML table the word..."Toddler" from the "Age Group" look up table when the event has multiple indexes stored as a string? I dont know how to inner join if the index from the event table is a string of indexes.

Any help would be great...

Sachin.Nand

2937 Posts

Posted - 2011-10-16 : 10:48:30

Change the tables design.It is not at all a good design to go with.Instead of storing values in piped separated format create 3 different columns for age group in the same table if age group is static.

If not then better create a new table and define the age groups in the new table and map those id's in the main table.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 14:00:20

search for normalisation and first normal form in google

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Armysniper89
Starting Member

6 Posts

Posted - 2011-10-16 : 15:00:05

I don't have control over the design. This is a CMS that is creating the custom tables so I must live within the confines of what they are making. If it were me, a separate table would be used to track entries for the age groups but I don't have control over this. So is there an answer to the question of querying the table other than reorganizing the table...since that is off the things I am allowed to do.

Sachin.Nand

2937 Posts

Posted - 2011-10-16 : 23:50:44

quote:
Originally posted by Armysniper89

I don't have control over the design. This is a CMS that is creating the custom tables so I must live within the confines of what they are making. If it were me, a separate table would be used to track entries for the age groups but I don't have control over this. So is there an answer to the question of querying the table other than reorganizing the table...since that is off the things I am allowed to do.

I am afraid then you will have to forget about indexes.Indexes can be used the best when you have designed the databases the way it is supossed to be designed.

PBUH

Armysniper89
Starting Member

6 Posts

Posted - 2011-10-17 : 01:15:52

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Armysniper89

I don't have control over the design. This is a CMS that is creating the custom tables so I must live within the confines of what they are making. If it were me, a separate table would be used to track entries for the age groups but I don't have control over this. So is there an answer to the question of querying the table other than reorganizing the table...since that is off the things I am allowed to do.

I am afraid then you will have to forget about indexes.Indexes can be used the best when you have designed the databases the way it is supossed to be designed.

PBUH

Not really helpful answer. Any good engineer should know that there are times when you have to work with data or structures that are out of your control. If you dont have an answer to the question, dont bother wasting your time and mine responding. I am looking for solutions not surrender because the parameters are out of your control sphere.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 01:47:57

quote:
Originally posted by Armysniper89

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Armysniper89

I don't have control over the design. This is a CMS that is creating the custom tables so I must live within the confines of what they are making. If it were me, a separate table would be used to track entries for the age groups but I don't have control over this. So is there an answer to the question of querying the table other than reorganizing the table...since that is off the things I am allowed to do.

I am afraid then you will have to forget about indexes.Indexes can be used the best when you have designed the databases the way it is supossed to be designed.

PBUH

Not really helpful answer. Any good engineer should know that there are times when you have to work with data or structures that are out of your control. If you dont have an answer to the question, dont bother wasting your time and mine responding. I am looking for solutions not surrender because the parameters are out of your control sphere.

Though what you've stated above has a point, an equally important point to consider is there are few standards that has to be followed if you want to get best out of your queries. Thats why whole concept of normalisation, indexing etc has been brought into. There's no use in worrying over performance of your queries if the root level db design is not as per recommended approach. If you've a bad design in place, whatever best you do, you wont be able to leverage upon the advantages of optimisation tips like indexing to your liking.

Anyways now that you have told you've live with current design, very little chance of getting an advantage using index here as your queries has to basically search for the pattern with long string of values stored in column. I think what you can try doing is to convert it to xml and use xml search methods or make use of parsing udf and parse out individual value and then do search.

ex:


select t.col
FROm yourtable t
cross apply dbo.prasevalues(t.field,'|')f
where f.Val=@yoursearchedvalue

Parsevalues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-17 : 02:37:56

quote:
Originally posted by Armysniper89

Not really helpful answer. Any good engineer should know that there are times when you have to work with data or structures that are out of your control. If you dont have an answer to the question, dont bother wasting your time and mine responding. I am looking for solutions not surrender because the parameters are out of your control sphere.

I am not going to diginify that with a reply...

PBUH

Armysniper89
Starting Member

6 Posts

Posted - 2011-10-17 : 10:44:38

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Armysniper89

Not really helpful answer. Any good engineer should know that there are times when you have to work with data or structures that are out of your control. If you dont have an answer to the question, dont bother wasting your time and mine responding. I am looking for solutions not surrender because the parameters are out of your control sphere.

I am not going to diginify that with a reply...

PBUH

Then please dont. Jeesh

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-10-26 : 18:05:41

quote:
Originally posted by Armysniper89

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Armysniper89

Not really helpful answer. Any good engineer should know that there are times when you have to work with data or structures that are out of your control. If you dont have an answer to the question, dont bother wasting your time and mine responding. I am looking for solutions not surrender because the parameters are out of your control sphere.

I am not going to diginify that with a reply...

PBUH

Then please dont. Jeesh

I'm pretty certain he mean "resolution", grunt-boy, but was just too polite. Doesn't help your case to come in here with an attitude, one that absolutely REEKS, yet asking for help.

DEFINITELY doesn't help naming yourself as a murderer for the military.

Armysniper89
Starting Member

6 Posts

Posted - 2011-10-27 : 02:18:42

quote:
Originally posted by Jim Beam

quote:
Originally posted by Armysniper89

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by Armysniper89

Not really helpful answer. Any good engineer should know that there are times when you have to work with data or structures that are out of your control. If you dont have an answer to the question, dont bother wasting your time and mine responding. I am looking for solutions not surrender because the parameters are out of your control sphere.

I am not going to diginify that with a reply...

PBUH

Then please dont. Jeesh

I'm pretty certain he mean "resolution", grunt-boy, but was just too polite. Doesn't help your case to come in here with an attitude, one that absolutely REEKS, yet asking for help.

DEFINITELY doesn't help naming yourself as a murderer for the military.

There was no resolution. To simply say to redesign the code is not helpful. That is like Dell Support telling you to reboot your computer. I was asking hoping for a more tactical support question and my "attitude" was based on repeated posts of limited value to the overall question. I am not a SQL guy so I asked for help from a place where I hoped to find some. I guess the land of SQL has very different ideas for help than software development. Anyways I solved it myself without help from them.

As for your comments about my military occupation...very nice comment. I hope you sleep well in your cozy bed under your desk or wherever you call your home knowing that people like me kept you safe. Night night and wake up and server your corporate masters.

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-10-27 : 16:25:59

As obtuse as you are (not a word I expect you, a member of a murdering organisation, to understand), I still am stunned at how you cannot understand what I meant by resolution.

As a decent human, I am bound by the rules of decency not to engage military filth in any kind of discourse. I will therefore end this exchange with a reminder to you that we, the decent civilians of the western world, pay your wages (most of us quite unwillingly, given how many women, children and innocent men you "people" murder), so, show some f**ing respect and know your place, guard dog.

Guys, I'm going to to write a letter to my representative...if we want to cut the deficit, we need to start with shutting down internet access in the Veterans' Homeless shelter.

Sachin.Nand

2937 Posts

Posted - 2011-10-27 : 16:46:26

Come on guys cool down...Lets not take this off topic.

Armysniper ..its great that you found a solution but remember we come here to this forum to help others voluntarily so you need to respect any suggestions,criticism etc and so should I.

If don't like any suggestion then just ignore it maybe someone else will come up with better one.But with your attitude and selection of words I bet no one will dare come forward to help you next time.We all are here to share and learn and not insult each other publicly.

If you guys still want to beat the hell out of each other then please do it offline.You can mail each other through the site.

PBUH

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-10-27 : 17:58:45

Totally cool here, Saach. Our non-civilian gatecrasher will be dealt with by a force MUCH greater than you, I or he for his crimes in Iraq/Afghanistan or wherever. Nothing I can say here will make his eventual fate worse. Just a shame he's not here in person, I'd love to look this military species in the eye before educating him about what is coming to him.

Thoroughly.

Sachin.Nand

2937 Posts

Posted - 2011-10-29 : 01:22:23

quote:
Originally posted by Jim Beam

Totally cool here, Saach. Our non-civilian gatecrasher will be dealt with by a force MUCH greater than you, I or he for his crimes in Iraq/Afghanistan or wherever. Nothing I can say here will make his eventual fate worse. Just a shame he's not here in person, I'd love to look this military species in the eye before educating him about what is coming to him.

Thoroughly.

No wonder why there always is an economic crisis looming over the US .Here are some interesting facts about the US military...

1) The United States spends more on its military than rest of the top 15 nations with the highest military expenditure. The rest of the list includes China ($119 bn), UK ($59.6 bn), France ($59.3bn), Russia ($58.7bn), Japan ($54.5bn), Germany ($45.2bn), Saudi Arabia ($45.2bn), India ($41.3bn), Italy ($37bn), Brazil ($33.5bn), South Korea ($27.6bn), Australia ($24bn), Canada ($22.8bn) and Turkey ($17.5bn). The US spends $698 billion. (Figures courtesy Stockholm International Peace Research Institute).

2) The total known land area occupied by US bases and facilities is 15,654 square miles -- bigger than Washington DC, Massachusetts, and New Jersey combined.

3)By 2033, the US will be paying $59 billion a year to its veterans injured in the wars

4) In 2007, the amount of money labeled 'wasted' or 'lost' in Iraq -- $11 billion -- could pay 220,000 teachers salaries

5) Defense spending is higher today than at any time since the height of Cold War

6) The yearly cost of stationing one soldier in Iraq could feed 60 American families for an entire generation

7) Each day in Afghanistan costs the government more than it did to build the entire Pentagon

8 ) In 2008, the Pentagon spent more money every five seconds in Iraq than the average American earned in a year

No wonder why US occupied Libya..

I know this is not the best place to put this but still...

PBUH

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-10-30 : 05:42:13

I remember interviewing in January for a DBA job with an aeronautics company. My first question was, "Do you supply parts for military planes (to whomever)?" They didn't, luckily. I will absolutely NEVER so much as connect to a database for any company that did. Not a click, not a keystroke.

I resent every penny I pay in taxes for the military. Well, there's nothing I can do about it except continue to write my Member of Parliament stiff letters about how much money we're wasting. But it's not the money; I would gladly pay taxes for reparations to be made to each of the war-shredded countries, and specifically to each family who has beloved parents/kids etc because our governments sent in a group of hit-men who care about nothing except murdering. If all that money you mentioned could bring back the people they murdered, then we should pay it.

You're right, this thread may be best moved to the conversation section.

Armysniper89
Starting Member

6 Posts

Posted - 2011-11-08 : 13:54:31

quote:
Originally posted by Jim Beam

I remember interviewing in January for a DBA job with an aeronautics company. My first question was, "Do you supply parts for military planes (to whomever)?" They didn't, luckily. I will absolutely NEVER so much as connect to a database for any company that did. Not a click, not a keystroke.

I resent every penny I pay in taxes for the military. Well, there's nothing I can do about it except continue to write my Member of Parliament stiff letters about how much money we're wasting. But it's not the money; I would gladly pay taxes for reparations to be made to each of the war-shredded countries, and specifically to each family who has beloved parents/kids etc because our governments sent in a group of hit-men who care about nothing except murdering. If all that money you mentioned could bring back the people they murdered, then we should pay it.

You're right, this thread may be best moved to the conversation section.

You guys disgust me. Dont bother responding...I wont return to this hovel. I actually abhore killing but I did what I had to do because I needed college. I went to college to get a career and then came back only to find my country gave most of the jobs to overseas useless developers or ones they imported here. So I have no care if you dont agree with my position...but atleast I did something to protect my country except trash it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources