|
Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId.
the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is-
categoryid sitepropertyid topcategoryid
---------- -------------- -------------
1 16 Null
2 16 1
3 16 2
4 16 3
5 16 1
I want the output to be like
Category1 Category2 Category3 Category4
--------- --------- --------- ---------
1 2 3 4
1 5
Please help me regarding this
|
|
|
|
|
Hi, your question is not so clear.
As of now what I understood, you need a row column transposition which can be achieved using PIVOT function of SQL SERVER .
You are saying that there are 4 level of categories.
In the output, that you presented here, I cannot find any sort of link between the first and second rows.
What is 5 doing in the second row? How is it related in this context? Does 1 will always appear in the Category1?
Is this 1, the TOPCATEGORYID and is it a demarcation indicating for a next row?
Category1 Category2 Category3 Category4
--------- --------- --------- ---------
1 2 3 4
1 5
Explain everything clearly... then it will be easier to answer your problem.
Niladri Biswas
modified on Friday, June 26, 2009 10:14 PM
|
|
|
|
|
Hi, yea may be I was not able to put my question properly. Let me try once more. Suppose CategoryId 1 is the root level, so thete is no top level category for it and it is null. The CategoryId 2 is the child of CategoryId 1, therefore, the topcategoryid for Category 2 is 1. Like wise for Category 3, the topcategoryid is 2 and for CategoryId 4, the topcategoryid is 3. CategoryId 5 is the child of CategoryId 1 and there are further no child categories under it.
Therefore, I showed thebase table like that. For the same, I want the output in the manner I depicted.
I hope I am able to explain this time.
Thanks for bearing with me.
-----Have A Nice Day-----
|
|
|
|
|
If you are using SQL Server 2008 then look into the hierarchy ID.
You actually have 2 problems, getting a reasonable hierarchy and then pivoting the result set. This may be of some use[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sorry for being late in answering your question.
I was completely bogged up with my project.
However, please try this
SELECT F.CATEGORY1,F.CATEGORY2,F.CATEGORY3,F.CATEGORY4
FROM(
SELECT C1.TOPCATEGORYID AS CATEGORY1,
C1.CATEGORYID AS CATEGORY2,
C2.CATEGORYID AS CATEGORY3,
C3.CATEGORYID AS CATEGORY4
FROM CATEGORY AS C1
LEFT OUTER JOIN CATEGORY AS C2
ON C2.TOPCATEGORYID = C1.CATEGORYID
LEFT OUTER JOIN CATEGORY AS C3
ON C3.TOPCATEGORYID = C2.CATEGORYID
WHERE C1.TOPCATEGORYID IS NOT NULL) F
WHERE F.CATEGORY1 = 1
N.B.~ The inner derived query is the right one. It is showing the right dependencies. Since I had to bring precisely to your format, so I applied the outer query.
However, it will work as per the input you gave and as per your desired output
Let me know in case of any concerns.
Niladri Biswas
|
|
|
|
|
Hi,
I did not run the query, but as a dry run, it looks to me absolutely perfect. On Monday, after reaching office, I will try it and will send you the response. Can you kindly send me ur email id.
Thanks
-----Have A Nice Day-----
|
|
|
|
|
Dear Neeraj,
I have tested that query and with varying inputs. It worked fine in all the situations.
The inner query is doing the necessary job. It is bringing out all the dependent records.
I wrote the outer query only to satisfy your requirement.
But anyways, I enjoyed to solve this kind of queries.
By the way, you can always mail me by clicking on the email link provided here in the message section
Niladri Biswas
modified on Saturday, June 27, 2009 11:16 PM
|
|
|
|
|
Hello,
i'm searching a few weeks for a database which fits to my requirements and where i don't have to pay any cent. MySQL is very great, but i don't know what's with the licenses, do i need to pay for the db-system or only for the connector or not and so on...
So i wanted to ask, is it a good idea to create a "own" database? Maybe, for each Table an own, maybe XML-File or CSV-File? I don't need a lot of relations and so on, it must be only fast. And it mustn't be very secure, because only a server is talking to my DB over TCP. The hole thing user-management, or index-keys and so on, that's coded very fast, and that's not the problem. The problem is, how to save that files? I mean, how does a database load their data? I don't access for each SELECT-Command the HDD.
So how to make it, i have a lot of ideas, but i don't know if it's makeable. Or maybe could somebody explain how a DB-System like MySQL works? They have all their DB-Table-Files soemwhere on the filesystem.
Many thanks.
|
|
|
|
|
Hi,
two suggestions:
1. for single-user applications, you could use Access; from a .NET perspective all this takes is an MDB file; once created (requiring Access as part of Office), you no longer need MS Access itself. Drawback is it has some functional limitations, and offers an SQL subset.
2. SQL Server Express is downloadable and free; it supports a limited number of users (10?) and a limited DB size (4GB?)
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Well that's the problem, i don't know how many users will use my database
It could be only 10 or maybe 1000 till 5000 And i have a lot data to save Not big but many, it should be used on a webservice or a web-application.
|
|
|
|
|
Have a look then at what your web host is offering.
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
It's my own server/s, or dedicated root servers.
So i "easily" need to storage data (a lot of data) and must access them pretty fast. Is there a way to do it on my own? What's with DataSet and XMLDataDocument? How is there the performance?
|
|
|
|
|
I can't tell you from my own experience, I only use a small MySQL db.
Most people here seem to favor SQL Server (the non-Express one for real applications).
This is something I copied here from earlier this year:
16-JAN-2009 Mika Wendelius, on choosing a database
- for single user application with small amount of local data => SQL Server Compact Edition
- for multi user application with medium amount of shared data => SQL Server Express / Oracle XE
- for multi user application with large amount of shared data => SQL Server Std or Enterprise / Oracle 11g
I believe that the next question you would have is: what is a small or large amount of data. Again a matter of opinion but perhaps some quantities (these are certainly not accurate but give some idea):
- small amount could be less than 1 GB, perhaps less than a hundred tables and no table contain over 10 000 rows
- large amount could be more than 4 GB of actual data, perhaps 500+ tables tables and and some tables contain over 10 000 000 rows. User amount is high, let's say over 1000 concurrent.
And maybe this could be useful:
Comparison of relational database management systems
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^]
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Hm OK, i think that my DB really wouldn't have more than 4GB this should really be enough.
My idea was, if i use a db-system, i had the idea to make run it on linux. So MS-SQL isn't the right DB for me.
|
|
|
|
|
Don't waste your time trying to develop a data storage engine. There have been hundreds of man-years invested in developing database engines. MS-Access is really quite fast and the license for MS-SQL server is really not that bad, if this is going to be a professional web application.
You speak of "clients" right ? Then they are paying a fee, right? Don't cheap out. Consider it an investment.
See my other post about starting with MS-Access and migrating to a more robust DB when you need it.
|
|
|
|
|
If it is a web application, then you could argue that there is only 1 user making an actual connection to the database; the web app itself! Remember that this is not a client server application where you need to create a user logon for everyone who wants to access the database.
Hmmm ... things to think about.
Consider this, it sounds like you really don't know how much data you are going to store or how frequently it will be accessed, so why don't you start with MS-Access as the database and make sure that you design you application in such a manner that it would be relatively easy to change database enginges without causing a massive re-write. It can be done. Make sure you have a good data abstraction layer designed and you could actually have your application capable of running on all kinds of database engines, MS-SQL, MySQL, Oracle. It all starts with the design.
Just a thought. 
|
|
|
|
|
Not bad, my application now, can work with all this connectors. I must only exchange them. But i for example don't know how it is with this MySQL DB licenses!
I have for example 2 Server, one is the DB-Server and one is the "processing Server" (the server where the server-side-application is running) which has a connection to the db-server (and there's the problem with only 1 user, maybe i want to make a server-farm so i have a lot of connections). The client is talking only to the processing server, so i don't give any libraries or something away. Maybe like the PHP<->MySQL relation, there i don't need to pay for it and could make commercial applications. But why does MySQL say, that if i use it commercial i need to pay for a enterprise-license.
Has somebody any idea with this problem? And, now which method is the best only to try to make a own db-system? Should i save it "Binary", something like this?:
Table-schema-file:
Table:Test
Field:ID,isKey="true",length="10",type="string"
Field:sample,isKey="false",length="5",type="string"
Data-File:
TestID1 Hello!
TestID2 Hallo!
So there in the Data-File i have my Data saved, each line is a new record. Byte 0-9 is my Field "ID" (because of length-1) and Byte 10-14 is the "sample"-field.
Or should i save it into XML and read/query and so on it with XQuey? Then is the problem, maybe i have a DB-File which has a size of e.g. 4GB. How to update my DB-Files? I couldn't save every 5 Minutes 4GB files, if i only changed for example 500KB? Is there any solution to do this? I found something like XmlDataDocument and DataSet which works exactly, if i would work with a DB. Has anybody experiences with this?
|
|
|
|
|
You don't save 4GB, you save only part of that file. You don't load whole file and then do something with it, you open partition of file this aprtions lets say is field in database, which has more space than it's filled (done so it can only add inside data, thats why database files are so large) and you fill that space with data. You use streams, you don't open file. And, it is really not that easy to make database engine. There is not only read-write, it's problem also in locking, so one does not owerwrite other user data, etc... auditing, transactions, etc...
With your knowladge, there is no way you could make database engine. Not at least you spend really long time on it. And let's say that 10 year licence for sql server would cost less then your working hours developing real database engine for 1 year
But if you insist on xml, etc... why not serialize objects to xml? Maybe a row by row as different file for faster loading, but many files wwould kill windows FS. Maybe create virtual FS. Oh, so much more to considere here, it is really not that easy to make your own database engine.
|
|
|
|
|
Yes, that's right, but i have only very simple and "foolish" data. Ok, something like logging and so on, that's right, this could be a problem, but i think, before i overwrite something, i could read it first to check if it's the correct line.
Yes, but i heard, that the XML-Architecture in .NET shouldn't be very fast. I saw a lot of tutorials for using XmlDataDocument in DataSet. I have a simple xst-file where is my schema and my xml-file for the data. With those both files i'm able to work with my data inside of DataSets.
But what's better? My "own" System, save all records byte for byte in a file with streams or use the XmlDataDocument and DataSet. My Application works now with SQL-Databases, so i want to try it with this system, and if i see, that really nothing works, i could buy a mysql or mssql license. Another question, what's with MSDNAA licenses? I get a MSDNAA license of mysql, can i use that? And is it able to use that, if i want the client to pay for their client-application? (Not for the server-app or the db, only for their user-account).
|
|
|
|
|
Another idea, which could interest a lot of developer here...
We have a object-oriented programming-language, why not a object-oriented database. With DB4O you can store the data "as it is" as a class into a DB. Something like i would do with XmlSerializer.
|
|
|
|
|
You wrote before about XML, fast? not really, but since you really would like to write your own database, and best way would be to have object oriented database, look into serialization that is in .Net, you can "convert" class to xml, and vice versa.
Also, take a look at CP articles, there is a lot of stuff on persistence frameworks.
This project really sounds fun, if you can afford it (by losing time, and not making any sallary out of it) go for it
|
|
|
|
|
Building your own multi-user database is a serious undertaking. I would only do that as a last resort, if I had some really unusual requirements that didn't seem to be handled by any of the existing databases out there. In fact, I probably wouldn't do it even then because I would question why my requirements were so unusual. I guarantee it will turn out to be a lot more work than you expect, and you will spend a long time trying to track down niggling little problems that don't show up until the system is under load.
The licensing terms for MySQL are a bit unclear: most people never bother reading them but just go ahead and use it anyway without thinking about whether they are in breach of the licence or not. Contrary to popular opinion, it is not universally free. Under certain circumstances you will need to buy a commercial licence.
To answer your question: at the end of the day, all databases store their data as a file on the disk somewhere. The exact location of that file and the format of the data inside it and how it is accessed varies from database to database. Database vendors put a lot of time and effort into tweaking this and polishing it and refining it, and they give you loads of configuration options so that your DBAs can tweak it and polish it and refine it even more to suit your requirements. Massive amounts of time and money have been spent trying to figure out the fastest, most efficient way to execute a SQL SELECT statement as a series of reads from the underlying disk files. It sounds trivial, but it's really not.
There are plenty of free databases out there, with very liberal licensing terms. Some are better for certain applications, some may suit your personal taste more. You don't say what technology you are using for your app, but you could take a look at Firebird, Postgres, SapDB, Apache Derby. They are all good databases with open licences. For some of them you can even download the source code and have a look at how they work under the covers if that's what floats your boat.
|
|
|
|
|
Consider looking at sqlite.
http://sqlite.org/[^]
_____________________________
When life hands you marmots, make marmalade.
|
|
|
|
|
I'm new to SQL and am getting an exception on my webpage when I have an argument to my SQL DB that has spaces in it.
Basically, the page is being passed a name like "Name with spaces", which is being sent as templatename: (in VB)
ra = New SqlDataAdapter("exec sp_GetTemplateByName '" & templatename & "'", TestStatConn)
ra.Fill(dt)
The exception gets thrown at this line only for names that have spaces, not if a templatename is passed like "name_without_spaces". The exception is
System.IndexOutOfRangeException: There is no row at position 0.
ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"
The sp_GetTemplateByName:
Select
..stuff..
from tbl_checklist_templates
where templatename=@TEMPLATENAME
order by stepnumber
Can anyone shed some light on what's going on here?
Edit:
If I remove the single quotes from this line
"exec sp_GetTemplateByName '" & templatename & "'" and make it
"exec sp_GetTemplateByName " & templatename & "" everything acts the same, except when I click on a template name with spaces, I get this exception:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '%'.
From line 55:
ra = New SqlDataAdapter("exec sp_GetTemplateByName " & templatename & "", TestStatConn)
(THIS is lne 55) ra.Fill(dt)
modified on Friday, June 26, 2009 10:43 AM
|
|
|
|
|
I guess the error is coming while the control reaches this line
ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"
Just check the count of your datatable, something like this(the code is in c#)
if(dt != null)
{
if(dt.Rows.Count > 0)
{
}
}
N.B.~ I don't know VB.net, henceforth presented the code in C#
Hope this helps
Niladri Biswas
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
