|
Not exactly a way to ensure you can get help in the future. Your sarcastic, rude response will be remembered, and will impact the likelihood that someone will help you in the future.
Clearly you had not read the "how to get help" at the top of the page before you posted, and Lecky was only reminding you quite properly of that.
Good luck in the future. Your crappy attitude should carry you far.
|
|
|
|
|
(1) SQL TUTORIAL[^]
(2) w3schools[^]
Hope this links will give an idea about the sql for you.
And these links cover the basic details.
Regards,
Satips. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus
|
|
|
|
|
Sweet, just what I need 
Thanks Satips
Dom
|
|
|
|
|
Glad to help always 
Regards,
Satips.
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus
|
|
|
|
|
|
suppose I have a table like this
create table MyTable(
varchar(10) s1,
varchar(10) s2
)
let an example for data in MyTable be
s1 s2
------------------------------
manager1 null
manager2 null
clerk0_1 manager1
clerk1_1 manager1
clerk2_2 manager2
clerk3_1 clerk0_1
clerk4_1 clerk1_1
clerk5_2 clerk2_2
clerk6_1 clerk4_1
the table says that s1 is the boss of s2. if s1 does not have a boss then s2 is null.
(I wrote a skeleton sql statement so that you can easily understand, please can you write a proc or sql statement)
I want a SQL statement like this
select s1 from MyTabe [please fill in here] where s1 = 'manager1'
so that the query will return
s1
---------------
manager1
clerk0_1
clerk1_1
clerk3_1
clerk4_1
clerk6_1
I mean starting from manager1 select recursively down to the last leaf. It is like given a tree select the root and all its leaves and the leafs of that subleaf and so on until all is out. Please note that there is no fixed level. The query ends when all the sub leaves are displayed. Can you please do it please?
|
|
|
|
|
rajugrover wrote: the table says that s1 is the boss of s2. if s1 does not have a boss then s2 is null
Then why not give the columns meaningful names. s1 = employee; s2 = manager
rajugrover wrote: I mean starting from manager1 select recursively down to the last leaf.
I've done this before - it is a difficult and time consuming query. SQL isn't so good at recursion. What I did was create a trigger so that when data was inserted, updated or deleted a denormalised version of the table was updated. If I needed to know everyone from PersonX downwards I could just query the denormalised table.
The denormalised table contains just two columns the superior and the subordinate. I'm presuming the table you show has more columns. The denormalised version can then be joined with the normalised version to get any additional data.
Unfortunately, off the top of my head, I don't remember how it was implemented so I can't give you code right now. But hopefully the above should guide you in a reasonable direction.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Colin Angus Mackay wrote: SQL isn't so good at recursion
THats why I love being able to use CLR stored procs in 2005. I try to stick with TSQL as much as possible but there are some problems, like recursion, that a bit of C# can do wonders with 
|
|
|
|
|
You aren't going to get anybody to actually do your work for you. It's up to you to figure out how to do it - if you don't, how are you going to be able to maintain it. I will give you a big hint here though, if you are using SQL Server 2005 - take a look at recursive queries with Common Table Expressions (CTE).
|
|
|
|
|
Pete O'Hanlon wrote: It's up to you to figure out how to do it - if you don't, how are you going to be able to maintain it.
Very valuable word of wisdom there, Pete.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thanks.
I sometimes wonder at these people. Have they no pride in their work? Do they never have to support the crap they foist on their clients?
|
|
|
|
|
Pete O'Hanlon wrote: Have they no pride in their work?
I am not sure. I don't mind helping when someone is stumped, but I do mind when they try and push their work off on people thinking they will get it for free.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
First, I think you meant s2 is the boss of s1.
As already mentioned, recursion (which is what you want) is supported in SQL Server 2005, and also in other products (Oracle, and DB2).
After much head-scratching, I finally just buried the Chain-Of-Command within a delimited text field for each employee (actually I was doing it for Bill-Of-Materials, but same principle); I called it the lineage.
CREATE TABLE employees(
ID INTEGER
, name VARCHAR(50)
, lineage VARCHAR(60)
);
When adding someone to the table, I need to know who they work for, then do:
INSERT INTO employees(name) VALUES (@mployee_name) ; then
UPDATE employees
SET lineage = (SELECT lineage
FROM employees
WHERE name = @manager_name) || ',' || ID
WHERE lineage IS NULL;
To get the full employee tree, I just selected with a LIKE:
SELECT * FROM employees WHERE lineage LIKE
(SELECT ID FROM employees WHERE name=@manager_name) || ',%' ;
(or something like that)
I ultimately decided that with the primary key on lineage, this would be substantially faster than using recursion since recursion would require grabbing data from all over the table, while in this method, the data is actually stored (via clustered index) under the manager.
Just FWIW.
David
|
|
|
|
|
I build an application that relies on SQL Notification to update a member in clients memory. I used a second select statement to read the members table after the original one because the original was using a view and multiple functions.
Works great.
But I was wondering for performance issues, is there a way to register for notification without returning a result set?
Right now I have the original statement :
SELECT [ID],[TemporaryID]...,ISNULL([dbo].GetProvinceName([ProvinceID], [CountryISO3], @Language), [Province]) AS Province...,[dbo].GetUserFullName([LastUpdateUser]) AS LastUpdateUser...
FROM [dbo].[vw_MembersFinder]
WHERE [LastUpdateDateTime] > @SyncDateTime AND
[Status] = @Status
So I read right after using :
SELECT [ID],[FirstName],[LastName],[Address1],[Address2],[City],[ProvinceID],[PostalCode],[CountryISO3],[ManInvalidAddress]
FROM [dbo].[Members]
WHERE [Status] = 'A'
This way I register correctly for notifications but of course I have to wait for two results sets.
Any good ideas?
|
|
|
|
|
Is it possible to delete a set of records by using WHERE clauses that access tables in two separate databases? I've looked all over the net and I cannot find a straight answer.
Thanks in advance
Brett A. Whittington
Application Developer
|
|
|
|
|
Here's a quick sample:
DELETE Item
FROM table1 WHERE MyValue IN (
SELECT MyValue2 FROM Database2.dbo.table2 WHERE MyCondition = 'Hello'
) The trick is to prepend the name of the database to the schema (the Database2.dbo bit in this sample).
|
|
|
|
|
Thanks for the quick response. My problem was how I was trying to use the 2nd database in the where clause. I was not trying it in an IN statement as your example shows. Awesome!
Brett A. Whittington
Application Developer
|
|
|
|
|
Same principal applies. Pretend it's exactly like a table in the same database but put a [Database].[Schema] in front of it like "database2.dbo.table"
|
|
|
|
|
Hello sir,
Please guide me on how to store data and access
that stored data in ASP.NET using SQLSERVER.
Please suggest me over this matter....its urgent
sandeephs
|
|
|
|
|
Sandep H S wrote: its urgent
Theres not much point in putting that. Most people here have plenty of other stuff to be getting on with thats more urgent than you and they'll get around to answering when they can. Quite often it'll even make people delay answering as it's considered rude.
Sandep H S wrote: Please guide me on how to store data and access
that stored data in ASP.NET using SQLSERVER.
Thats a very broad subject with lots of articles and resources available. Theres a whole section here on CP about it:
http://www.codeproject.com/cs/database/[^]
Have a go with that and if you have any more problems that are specific (i.e. whats the best practise for disposing SqlCommand objects?) rather than broad questions (i.e. how do i learn to program) then we'll be happy to help.
|
|
|
|
|
Instead of updating in usual way, i use a simple binding for my data entry. It works but my problem is the last record that i recorded did not update. Only the previous records are updated, but the last did not.
my code for 'update' button is:
Me.BindingContext(DataSet21).EndCurrentEdit()<br />
Me.OleDbDataAdapter2.Update(DataSet21)<br />
MsgBox("Grade recorded!")
|
|
|
|
|
Rharzkie wrote: DataSet21
Rharzkie wrote: OleDbDataAdapter2
Wow! Have you ever thought of actually giving your variables descriptive names? What does DataSet21 mean to anyone anyway?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
It's the result of dropping data access components onto the form designer, which I would go as far as to call worst practice.
|
|
|
|
|
You can still edit the properties and give it a decent name without ever having to go near the code. Whilst I agree that the drag n drop data components are evil, thier use is no excuse for bad naming
|
|
|
|
|
Just answer my question ok..
To run your program properly and to write the correct codes are more important that giving a descriptive name. I can run my program properly even though i did not change the name of my variables...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
