|
I have a table Named 'Roles' which contains 'ID' and 'ParentID' fields.
Some Sample Data is
ID ,Name ,ParentID
1 ,Admin ,0
2 ,Manager ,1
3 ,Front Office User ,1
4 ,Supervisor ,2
5 ,Inventory User ,0
6 ,Human Resource User ,4
I would like to extract data of Parent and all children.
SELECT b.[ID] as 'Parent', a.[ID] as 'Role'
FROM Roles a
INNER JOIN Roles b
ON a.ParentID = b.[ID] and a.ParentID=1
which can return only.
ID ,ParentID
1 ,2
1 ,3
My wanted form is
ParentID ,ID
1 ,2
1 ,3
2 ,4
4 ,6
Like an organization tree, the top parent level can access all lower level nodes.
Sql 2005 support that feature.
How can I access them in Sql2000 recursive function or store procedure?
Thanks
May Thu San.
|
|
|
|
|
I think you made a little mistake in your query. This should work:
SELECT b.[ID] as 'Parent', a.[ID] as 'Role'<br />
FROM Roles a<br />
INNER JOIN Roles b<br />
ON a.ParentID = b.[ID]
Wout Louwers
|
|
|
|
|
Sorry, My Query is
SELECT b.[ID] as 'Parent', a.[ID] as 'Role'
FROM Roles a
INNER JOIN Roles b
ON a.[ParentID] = b.[ID] and a.ParentID=1
Some Sample Data is
ID ,Name ,ParentID
1 ,Admin ,0
2 ,Manager ,1
3 ,Front Office User ,1
4 ,Supervisor ,2
5 ,Inventory User ,0
6 ,Human Resource User ,4
The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc.
How can I get all children?
|
|
|
|
|
May Thu san wrote: The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc.
How can I get all children?
by removing the "and a.ParentID=1" from the query.
|
|
|
|
|
I think I understand what you want now. But this cannot be easily solved whit the ParentID. This[^] article should help you.
Wout Louwers
|
|
|
|
|
how to write the query to get the records in between dates
|
|
|
|
|
SELECT * FROM TableName WHERE DateFieldName BETWEEN '1-JAN-07' AND '31-JAN-07'
|
|
|
|
|
how to use sql in host languages like c / c++
|
|
|
|
|
Have you had a look at LINQ?
|
|
|
|
|
how to display all tables from any single database in sql server 7.0.
|
|
|
|
|
select * from Sysdatabases where xtype='u'
|
|
|
|
|
hi,
what are the lock types available with "recordsets" and How different are they from “datasets”
Thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Please, help me in solving this query
I have a table some thing like this
Sens_Name T2_Value Q_Value CMI_Value
---------------------------------------------------
Sens 1 12 70 80
Sesn 2 13 71 81
I want to query the above table so that I get an output in this way
Sens_Name Stat_Value Stat_Type
--------------------------------------------------------------
Sens 1 12 T2_Value
Sens 1 70 Q_Value
Sens 1 80 CMI_Value
Sens 2 13 T2_Value
Sens 2 71 Q_Value
Sens 2 81 CMI_Value
Thanks in advance
Irsh
|
|
|
|
|
You can use a UNION clause:
SELECT
Sens_Name,
T2_value AS Stat_Value,
'T2_Value' AS Stat_Type
FROM
SensTable
UNION
SELECT
Sens_Name,
Q_value AS Stat_Value,
'Q_Value' AS Stat_Type
FROM
SensTable
UNION
SELECT
Sens_Name,
CMI_Value AS Stat_Value,
'CMI_Value' AS Stat_Type
FROM
SensTable
ORDER BY
Sens_Name,
Stat_Value
|
|
|
|
|
|
Im trying to establish an ASPState database with the aspnet_regsql command. I receive an error informing me that the wizard was unable to connect to the sql server. I have an express server installed and have no problems connecting to it otherwise. On msdn it says that
The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true).
Im not sure what that means?
I have also made the preparations with the T-SQL commands so I could enable the Agent XPs SQL Server option with the following commands
EXECUTE sp_configure 'show advanced options', 1<br />
RECONFIGURE WITH OVERRIDE<br />
GO<br />
<br />
EXECUTE sp_configure 'Agent XPs', 1<br />
RECONFIGURE WITH OVERRIDE<br />
GO<br />
<br />
EXECUTE sp_configure 'show advanced options', 0<br />
RECONFIGURE WITH OVERRIDE<br />
GO
Can anyone help?
|
|
|
|
|
Hi,
i created user with access to read only database. Its possible to make that only this one user (rest of users from LAN only) will have access to database from WAN?
Greetings
|
|
|
|
|
If you're asking if SQL Server can control this, the answer is no. If a user can connect to SQL Server and present the correct credentials, they will be authenticated. It doesn't matter where they connect from.
Are you using SQL Server authentication or Windows Authentication? If you're using SQL Server authentication then you have no control over this - which is why Windows Authentication is the recommended best practice. If you're using Windows Authentication, then you should be able to control this by configuring which WAN accounts have access to your LAN and which of those WAN accounts which have access may also access SQL Server.
|
|
|
|
|
Kindly help me out regarding this. pls tell me the sql query for this..
Bawa
|
|
|
|
|
See Here
OR
Here
Hope it will provide some kind of information....
Regards,
The only programmers that are better than C programmers are those who code in 1's and 0's.....
Programm3r
|
|
|
|
|
Thanks for replying... but I can't open these links my firewal does't support :(these links.
Bawa
|
|
|
|
|
Normally, it's along the lines of
INSERT INTO table1
SELECT values FROM table2
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks !!
Bawa
|
|
|
|
|
hi, I've this problem :
we work with a sql database which operates in accent-insensitive mode,
once I've cached some data from it to my .NET application, how can I search within this data (contained in a DataTable) with an accent-insensitive query?
I mean I'd like to obtain the same result (i.e.) " André "
in any case if user is searching for:
DataTable.Select("name LIKE 'andré')
DataTable.Select("name LIKE 'andrè')
DataTable.Select("name LIKE 'andre')
thanks to anyone may answer
Darx
|
|
|
|
|
Not sure which CultureInfo to use but I'd recommend setting the Locale propery of the DataTable. Try Invariant culture setting - it should ignore accents.
|
|
|
|