| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2003-12-09 : 10:22:38
|
| I have a table called "items" and I want to pull each colom from it that does not contain the word "None". What is the easyiest way to do this? There are about 14 coloums.--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-09 : 12:00:36
|
| So your saying the easyies way would be like this:select * from items where item1 <> "None" or item2 <> "None.... though all the coulm names?Please keep in mind the select statment will be used in an ASP script to output the list of items to a website.--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-09 : 12:15:21
|
| If you can, try normalizing your database .... is this a table or a view with columns such as Item1, item2, item3? Typically, each of those "items" should be a seperate row in a table, and then from there can filter easily (its all in the same column, then). And, then, for presentation purporses, you can cross tab the results as columns if you wish.- Jeff |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-12-09 : 12:20:05
|
| I think Eagle has something like this :itemsCOLUMN1, COLUMN2, COLUMN3, COLUMN_NONE_1, COLUMN_NONE_2, COLUMN4 etc etcor did I misread the question?!________________Make love not war! |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-10 : 01:16:51
|
| It is a table named "items" with 14 coulms each named "item1" "item2" and so on. Each will have the name of an item a player has bought, but if he only has say 10 items then item11 - item 14 colums contain "None" I want to use an SQL quriey to pull the colums down that don't have "None" in them (so only the columns with actule items in them) and then us ASP's getrows() command to fill an array with them. The hole point of this is to print out "item1, item2, ect" for all there items and still have the "," in there.--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
TimChenAllen
Starting Member
45 Posts |
Posted - 2003-12-10 : 03:27:43
|
| Eagle_f90, post the DDL for the table you are interested in.If you don't know how to do that, do this: in Enterprise Manager, find the table and right click it. Select "All Tasks"->"Generate SQL Script" then click "Preview". Then click "Copy". Paste the code in a message. Please use CODE tags around it. Slow down when you ask for help and you'll get better help. Thanks.--Timothy Chen Allenemail me if you have a job in New Orleans for me[url]http://www.timallen.org[/url] |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-11 : 00:33:15
|
Ok, here is the code generated by doing what you said, keep in mind not all the item tables are there yet and there are some tables that I don't even want looked at by the select statmetn (anything not named playerid or itemX [where x is a number]).if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[items]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[items]GOCREATE TABLE [dbo].[items] ( [playerid] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [item1] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ietm2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [item3] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [item4] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [item5] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [bank1] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [bank2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [zeny] [numeric](18, 0) NOT NULL , [bankedzeny] [numeric](18, 0) NOT NULL , [reciving] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [currentday] [numeric](18, 0) NOT NULL , [total] [numeric](18, 0) NOT NULL , [recivingfrom] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO --For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams.P.S. I know the layout of the DB is horrible. This is not ment to be an optimized DB. I am just getting the DB together so I can get the game running ASAP. I will optimize it later. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-11 : 07:38:35
|
quote:
I am just getting the DB together so I can get the game running ASAP. I will optimize it later.
In application programming, we often hear (and use) the phrase "premature optimization is the root of all evil". Which makes sense.But not for databases. It is MUCH better to get a decent database design up front, because once you change it, not only is it going to be hell to migrate all your data, but you will have to change all your application code as well.Not only that, but the way you are doing it now is the hard way. If you design the data model right, you won't have the issues you are asking about now.So, let's recap. If you have a rethink of your data model (and we love to help with that) :1. Your current issue is solved2. You won't be in a world of pain when you come to do it right.Damian |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-11 : 08:48:00
|
| What if there is less than or more than 5 items for a player? can this happen? Breaking out those items into a related table, in which each item is a ROW (and not a column) will give you much more flexibility. And, it will allow you to search through ALL items to solve your original problem. And, then you can easily get counts of all items per player, or which players have a certain item, very easily without having to search through all those different columns. And you can delete a certain item or change it easily, again, without having to search through multiple columns.Hopefully, these a primary key on the table you've posted? Without knowing that, it's hard to see what this table is used for.Trust Damian -- what he says makes sense. If you have a good DB design, your application and your SQL almost writes itself.- Jeff |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-11 : 10:52:41
|
| The reason the items are in columns and not rows is because the players a limited to a set amount of items. At the moment nothing is realy being optimized. Once the game gets up a new DB will be created to add a lot of featuers and all the code will be rewritten for optimization. At this point in time I just want to get the basic features up so the players can get back to the game. I am not worried about migrating the data, it is pritty easy for me to do that. All I want to do right now is load the items into an ASP array so I can print it out to the page.--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-11 : 17:36:33
|
quote:
limited to a set amount of items
Words I have learnt to dread.5 always becomes 6, a "one off" never is.I don't understand why you are so intent on doing things the bad way.Damian |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-11 : 20:15:03
|
quote:
Originally posted by Merkin
quote:
limited to a set amount of items
Words I have learnt to dread.5 always becomes 6, a "one off" never is.I don't understand why you are so intent on doing things the bad way.I don't uderstand what you mean by "5 always becomes 6, a "one off" never is."I am not intent on doing things the "bad way". I just want my game back up and running. I will worry about optimizing and doing it the "good way" when I have more time. And what might be bad to you might be good to others.Now to the issue at hand, besides everyone telling me to change my way to make things easyers can anyone tell me the solution to my issue with out having to redo the DB? I WILL NOT CHANGE THE DB LAYOUT AT THIS TIME, NORE WILL I UNTILL I AM READY FOR THE NEXT STAGE OF THE GAME LIKE I ORIGINALY PLANED!Damian
--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-12-11 : 20:32:19
|
| What everyone is trying to tell you, if you STOP YELLING AND LISTEN TO THEM, is that your current design IS BAD, sticking with it will make things WORSE, and you WILL NOT MAKE THE TIME TO FIX IT LATER. If you had more experience designing databases you would already know that the time to "fix it later" never comes.Indeed, if the design was OK to begin with, why is it that you "just want my game back up and running." What caused it to fail in the first place? Why are you having so much difficulty doing what you want with the current design? If these are not warning signs that something's wrong, you have even deeper design problems than this one.There's a nice bit of wisdom from Ron Soukup, who was the SQL Server project manager at Microsoft, that many developers "...believe there's never time to do things right, but always time to do them over." And he's absolutely right, on both counts. Ignore his and everyone else's advice if you want to be one of those developers. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-11 : 20:35:06
|
quote:
I don't uderstand what you mean by "5 always becomes 6, a "one off" never is."
Get a job in software dev for a while, and you will  Seriously dude, drop the attitude. You asked for the "easyiest way". The easiest and best way, is do it right the first time. We have spent a lot of time with you in these forums finding workarounds for what are design flaws. If you actually take the time to listen to us and fix it, you won't have half the problems you have asked us about.Damian |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-12-11 : 20:45:01
|
| Well, hey, since we're all having such a good time, let me pile on too.1) If your application has not gone live yet, then NOW is definitely the best time to change the design.2) The reason you have not gotten a good reply to your original question is because there is no good reply to it. You cannot return a resultset where the number of columns varies on every row. And if you're somehow guaranteeing (impossible without a Primary Key) that you'll only get one row back in your query, then the manipulation you seek is much better done in your ASP page than in the SQL query. If you want to use optimized functionality, like ADO GetRows or GetString, then you need to design your database in such a way that those functions work with it. It's your choice. Which is more important to you? A good design as has been described here, or using a function that won't work with your design?--------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url] |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-11 : 22:17:16
|
| The reson I am yelling is because I am tired of everyone telling me to change it. I am not changing it now for the resons I stated already, I want my game up and running. The last version did not fail, the last version was all text based files I had to update every night. It had finaly reached a point where it was time to restart it. After restarting it I decided to do a DB version of it, that was almost 2 years ago. All that time I ahve been telling everyone it will be running soon, and I am tired of saying that. I just want it running so people can start playing it again. I know for a fact I am going to redo the DB and the code after the initial launch because I am going to take the time to relearn ASP and T-SQL and then use the game as a working base to apply the new information into practice.You are aright i did ask for the easyiest way which would be to redo the DB but I have also stated I don't want to redo the DB so that should tell you I need the easyiest way to do it with out redoing the DB. I don't know what a primary key is but I can tell you that the fild called "playerid" is a unique value in which contains an ID for a particular player that all the select statments look at to pull the right info for the right player.So can someone tell me how to get the info I need into an array in ASP so I can print it out?--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-11 : 22:44:14
|
 Damian |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-11 : 23:14:09
|
quote:
Originally posted by Merkin
Damian
I am taking that as no one can give me the info. Well thanks, it has become apparent no one wants to help me untill I make the DB "perfect" so I won't ask anymore questions.--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-11 : 23:22:46
|
| It's not that.It's that you ask advice then ignore it because you don't like the answer.Also, most of the site's users are probably asleep by now. Maybe tomorrow someone will be prepared to help you do a substandard job, but it won't be me.Damian |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-12-12 : 00:07:34
|
| Like I said, since no one seems to want to help me untill i get my DB "perfiect" I am not going to bother you people with questions anymore. Why you guys are pissing over the DB design now I don't know, no one ever did that 2 years ago when I first tryed to do this (lost taht info in a HD crash of my system).--For those with wings, fly to your dreamsDearms are what are found at the end of reality, and your reality if what is found at the end of your dreams. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-12 : 00:14:40
|
| Actually there is a good answer in this thread already (other than the answer you should use), you are just ignoring it. See if you can guess which one.Secondly, if you had the question answered here already, use the searches for any threads you started. It will still be here.Damian |
|
|
|
Next Page
|
Pull all filds form table that DON'T contain word.