|
A very interesting problem.
Most likely you can solve this by creating a User-Defined function (or Stored Procedure) that keeps calling itself to union all the result from the ChildIDs.
How about something like below (example of usage: "SELECT ID, ModuleID FROM dbo.GetModule('91')").
Note: I haven't tested the UNION yet. You might need to modify it a bit to make the union work.
CREATE FUNCTION dbo.GetModule
{
@ListID nvarchar(2000)
}
<code>--Concatenate all ChildIDs of all IDs in @ListID. Basically COALESCE is used to replace NULL with '' (empty string)</code>
DECLARE @ChildIDs varchar(100);
SELECT @ChildIDs = COALESCE (@ChildIDs + ', ', '') + ChildIDs
FROM tblModule WHERE ID IN (SELECT value FROM dbo.Split(@ListID)))
<code>-- Selects the Parent's ID and ModuleID, then UNION with ChildIDs' result if there are ChildIDs</code>
IF @ChildIDs = null
SELECT ID, ModuleID --This is to return only parent result if there are no ChildIDs
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
ELSE
SELECT ID, ModuleID
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
UNION --This is the recursive code to union with result of ChildIDs
SELECT ID, ModuleID
FROM dbo.GetModule(@ChildIDs)
RETURN
Of course, you also need the Split UDF to split your ChildIDs into array of IDs that is acceptable in SQL:
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT
Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
Signature
The only valid use of the BLINK tag: Schrodinger's cat is <blink>not dead .
-- modified at 1:38 Tuesday 20th September, 2005
|
|
|
|
|
Hi edbert!
Now that u've mentioned it, it does look like an interesting problem. i had an inital impression that it was pretty much exhausting.
well im glad u understand my problem...i was strugling on how i could explain that as clear as posible im not even sure it was but thanx for ur reply.
I got the concept on what ur telling me. I've to create a function that separates my childIDs and loops until every single one is displayed. impressive thinking! unfortunately i didnt quite understand the code you posted. My bad i forgot to tell you im using MySQL database and vb.Net as my backup code i just figured this is a query problem, why not post it in SQL thread . and to top thing of im still a beginner programer . I wasnt even sure if...then statements were available in SQL commands. If its not too much trouble and if u've the spare time could you pls explain briefly the code you posted. but if u cant i'd understand completly.
thanx so much for your help edbert! i realy appreciate it!
Aim small, miss small...
|
|
|
|
|
Well, I'll try to explain as much as I can.
Basically there are 2 functions there (GetModule and Split).
The Split functions is used to convert from text (e.g. '1,2,3,4') into a table with 2 columns (Id, Value).
So, splitting the value of '1,2,3,4' will result in:
Id | Value
1 1
2 2
3 3
4 4
This is because SQL statement CAN accept e.g. SELECT * FROM [TABLE] WHERE ID IN '1,2,3,4' but it CAN'T ACCEPT SELECT @ FROM [TABLE] WHERE ID IN @ListOfIDs.
Notice that the only difference is @ListOfIDs which contains the value '1,2,3,4' as varchar/string.
Therefore, @ListOfIDs need to be split into several rows using the Split UDF before it can be used in an SQL WHERE condition.
You don't need to dwell too deep in Split function right now, but if you need further explanation let me know.
The second function, GetModule, is used to get the Id and Module of the parent row (the first ID you specified), and then tries to concatenate the children ids into the result.
CREATE FUNCTION dbo.GetModule
{
@ListID nvarchar(2000)
}
The code above basically says we want to create a User Defined Function called GetModule belonging to dbo user. It accepts a parameter of nvarchar (or unicode string) with length of 2000.
DECLARE @ChildIDs varchar(100);
The above declares a variable called @ChildIDs which can have a varchar up to 100 in size.
--Concatenate all ChildIDs of all IDs in @ListID. Basically COALESCE is used to replace NULL with '' (empty string)
SELECT @ChildIDs = COALESCE (@ChildIDs + ', ', '') + ChildIDs
FROM tblModule WHERE ID IN (SELECT value FROM dbo.Split(@ListID)))
The above tries to select all the ChildIDs columns from the tblModule and combines them, so for example if your ID (in the @ListID variable) is 91, then it will result in '911,912,913,915'.
But if your @ListID contains '90, 91' it will return the ChildIDs as a combined string which is '901' + '911,912,913,915' ending up in '901,911,912,913,915'.
COALESCE is basically a function that checks if @ChildIDs is null then return '' instead of returning null. You can replace it with IsNull function.
After we get all the child ids of our rows, we then check whether there are child ids or not.
-- Selects the Parent's ID and ModuleID, then UNION with ChildIDs' result if there are ChildIDs
IF @ChildIDs = null
SELECT ID, ModuleID --This is to return only parent result if there are no ChildIDs
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
The function above says if there are no child ids, then just return the parents' ID and ModuleID (e.g. if the parent ID is 91, it will return '91', null).
ELSE
SELECT ID, ModuleID
FROM tblModule
WHERE ID IN (SELECT value FROM dbo.Split(@ListID)
UNION --This is the recursive code to union with result of ChildIDs
SELECT ID, ModuleID
FROM dbo.GetModule(@ChildIDs)
In the code above we check if there are child ids, then we should combine (UNION) both the parent result and the children results. The children results will be taken by calling the GetModule and passing the children's ids of the parent's id.
AFAIK, IF and ELSE also exists in MySQL as they are standard SQL syntax. Coalesce might not, but you can replace it with IsNull or a similar function that checks if a value is null then return something else.
That's my explanation so far.
Try each part of the code one by one to have a better understanding of them (e.g. the split function, the function to return child ids).
I hope that helps.
Ed
|
|
|
|
|
wat can i say...have u ever think of authoring an article about certain tutorials? crystal ed!
Its amazing how one can learn tons of stuff online...and they say computers were a waste of time. But with explanations like this how can one argue with the results
thanx ed! the way i see it, this program is just beginning...
Aim small, miss small...
|
|
|
|
|
No worries. I might actually write a tutorial on SQL tricks because I see that some people might need it.
Just one word of note, try learning update with parameters in ADO.Net, creating stored procedures and user defined functions.
You'll need them as a programmer.
Ed
|
|
|
|
|
I can fill the DataGrid from a data source SQL but the update fails...
New to Programming and working on SQL DB any help would be great...
Dim SQLConnection1 As SqlClient.SqlConnection
Dim SQLCommand1 As SqlClient.SqlCommand
Dim SQLAdapter1 As SqlDataAdapter = New SqlDataAdapter
Dim myDataSet As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
SQLConnection1 = New System.Data.SqlClient.SqlConnection
SQLCommand1 = New System.Data.SqlClient.SqlCommand
SQLCommand1.Connection = SQLConnection1
SQLConnection1.ConnectionString = "Data Source=JAPEL-XP;Initial Catalog=CanonDB;User Id=sa;Password=;"
'SQLConnection1.Open()
End Sub
Private Sub FillData()
Dim Command1 As SqlCommand = New SqlCommand("SELECT Username , [Password] FROM WebSiteList")
Command1.CommandType = CommandType.Text
SQLConnection1.Open()
Command1.Connection = SQLConnection1
myDataSet = New DataSet("myDataSet")
SQLAdapter1.SelectCommand = Command1
SQLAdapter1.Fill(myDataSet, "WebSiteList")
DataGrid1.SetDataBinding(myDataSet, "WebSiteList")
SQLConnection1.Close()
End Sub
' This is the Code that fails..................
Private Sub UpdateData()
SQLAdapter1.Update(myDataSet, "WebSiteList")
End Sub
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
hi there,
could u specify more detail about the problem u r facing?
<< >>
|
|
|
|
|
You first need to set the UpdateCommand property of the SqlDataAdapter to a SqlCommand that will update the database with the contents of the DataSet (having parameters for each table column that will be updated).
You also need to set the InsertCommand and DeleteCommand properties.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
I understand what you are saying but am not sure how to do this...
Can you point me to an example
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
There's some example code in here:
http://www.codeproject.com/cs/database/sqldawithoutsqlcb.asp?[^]
The code is not so great, but basically there's an overload when creating the paramters for a command (insert, update or delete) that specifies the column name from where it's value will be taken.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi guys
i m having a table which primary key is char(7). i want to have last inserted row's id value but @@IDENTITY returns numeric rather character. I dnt hv ne field like time timestamp too 
Thanks in advance
Utsav
|
|
|
|
|
do i need 2 mention thaT CHAR primery key is not identity (i.e. autoincrement)??
|
|
|
|
|
i think it was d stupid most question a person can ask, got d solution guys, dnt boter 4 that stupidness
Utsav
|
|
|
|
|
hi there,
if u want to use @@Identity to get the last inserted record, u've to use the int field with auto increament attribute.
Then, u can use this identity to retrive the info of the last inserted record
<< >>
|
|
|
|
|
Hi,
I have a field in a table that can have one of three values; the field is multiple choice, in other words. I was wondering how I can represent this in a DataTable. The closest I've been able to come is to create one table with one field and three records representing the three values. In a second table, I create a data relation between the multiple choice field and the table with the three records. Therefore, the field in the second table must have one of the three values. But I'm guessing there is probably a better way? Thanks!
|
|
|
|
|
Leslie Sanford wrote:
to create one table with one field and three records representing the three values. In a second table, I create a data relation between the multiple choice field and the table with the three records. Therefore, the field in the second table must have one of the three values.
My preference would be to normalise that. Unless the possible values are integers or single characters I'd want to kave an id column.
The Main table has a column that is an id to the Lookup table. The Lookup table has two columns, an ID column (a tiny int, because that is all you need) and a Value column (of what ever type you need).
The reason for the tiny int is so that the reference takes up as little room in the Main table as possible (I'm guessing there is going to be a lot more rows in there than in the Lookup table) which will improve database performance if a lot of rows have to be accessed as they will take less space and the database will be able to read them from disk faster (and disk access is usually the bottle neck in database systems)
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Colin Angus Mackay wrote:
Does this help?
Yes, absolutely! Thank you very much.
|
|
|
|
|
That sounds OK to me.
As far as I can see, you can either create a table listing all the choices available and link it to another table (like what you did), or if the selection numbers are fixed (e.g. always 3 selections) then you can have one table with 3 columns each containing a value.
|
|
|
|
|
I have a problem with the stored procedure that does the union on columns in from one table to another.
I have table1 which has the following column names:
A11, A12, A13, A21, A22, A23
and what the union does is to insert the records from table1 to table like this:
insert into table2
as
select A11 as a1, A12 as a2, a13 as a3
from table1
union
select a21 as a1, a22 as a2 , a23 as a3
from table1
The procedure is working but what it does, if I have one record in table1 it duplicates the record after the union in table2.
That is for one record in table, I get two records of the same record in table2.
What might cause this?
Please help as this gives me a lot of work in identifying the duplicates after running the procedure.
Thanks
phokojoe
|
|
|
|
|
You are unioning a different set of columns on the same table so it sees them as distinct. If you were unioning the same set of columns then it would remove the duplicate rows.
If you do not want the same row being represented twice then you need to put in a WHERE statement to each to define what is permitted and what isn't.
Alternatively you could perform two INSERT statements*. e.g.
INSERT INTO table2
SELECT [id], A11 AS A1, A12 AS A2, A13 AS A3
FROM table1
INSERT INTO table2
SELECT [id], A21 AS A1, A22 AS A2, A23 AS A3
FROM table1
RIGHT OUTER JOIN table2 ON table1.[id] = table2.[id]
WHERE table2.[id] IS NULL
However, you still need to define conditions as to what is acceptable and what isn't for these two inserts as the second INSERT will currently not add anything as each row will already be represented. Also, table2 needs a primary key that is shared with table1. I've assumed that to be a column called [id], you may need to change that.
* DISCLAIMER: I've not tested this so it may not be 100% correct. I tend to get my right and left outer joins mixed up
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
this is a standard line code for mysql database connection
i've created a database, fisically i don't know where is the file of my database, so i don't know how indicate it in this row.
I think i need to put it here "server=serverurl" but what insert at the place of serverurl?
SqlConnection myConnection = new SqlConnection("user id=username;" +
"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");
|
|
|
|
|
At the Enterprise Manager, when I click on a particular database, the error message was:
Internet Explorer Script Error
An error has occurred in the script on this page
Line 307
Char 2
Error: Unspecified error
code: 0
url: res//C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Binn\Resources\1033\sqlmmc.rll\Tabs.html
Clicking on other databases doesn't yield this problem. I have tried deleting the database, restoring it. Even tried using wizard to create a new database and add back all the files but the error still persist.
Any way I can get rid of the error. I still can work on the tables but it is irritating as it will pop up now and then.
|
|
|
|
|
Dear friends
i have a database which has a lot of columns and unfortunately all columns has a some null input.
Database has a lot of columns and it is not easy to write in where criteria all field.
I need if is possible a query like this all of in database table which data is null change it with "NO INPUT" string.
If i can take your valuable advice(s) i will be very glad.
Thanks a lot
|
|
|
|
|
MAGICIANMERLIN wrote:
I need if is possible a query like this all of in database table which data is null change it with "NO INPUT" string.
That's not an easy query. Why not try your first option?
MAGICIANMERLIN wrote:
write in where criteria all field
With a little lateral thinking this can be achieved in less than 5 minutes.
SELECT 'UPDATE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] SET ['+COLUMN_NAME+
']=''NO INPUT'' WHERE ['+COLUMN_NAME+'] IS NULL;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE='YES' AND DATA_TYPE IN('char','nchar','varchar','nvarchar')
Basically, this is a query that will generate a SQL script. Run it in Query Analyser and remember to set the Results in Text (Ctrl+T) then run it. You can then copy the contents of the results pane to use as the query that will update your data.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thank you Collin,
i am still trying that solution which u can advice to me.
Still i can not solve my trouble but i try and i think i ll do it.
Thanks a lot
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
