|
Hello all.
I'm almost getting my head to the wall  because I can't get to the solution for a query I need to do.
Is anyone here wanting to help me ?
Let me explain the problem:
I have a table named T1 with the following structure:
ID | Subject | ParentID
This table represents info in a form of tree and when I'm at the top branch the ID and ParentID fields have the same value.
If I have the following data
ID Subject ParentID
1 Sub1 1
2 Sub2 2
3 Sub1_1 1
4 Sub1_2 1
5 Sub1_1_1 3
I want to make a query with the following result:
ID|Subject|NumberOfChildRecords
e.g.:
1 | Sub1 | 2
2 | Sub2 | 0
3 | Sub1_1 | 1
4 | Sub1_2 | 0
5 | Sub1_1_1 | 0
Is this simple to do ?
Thanks in advance for the attention you are having reading this.
|
|
|
|
|
I dont think it is simple... but you could certainly try something like:
Here is how you would do it using NW:
select a.employeeid, count(b.employeeid) from employees a, employees b
where a.employeeid = b.reportsto group by a.employeeid
What we have is a self referencing table. What we need to do is essentially add the table twice in the from clause with two seperate aliases and then correlate the two in the where. I should be using ANSI standard joins here, but it was quick.
so for you we need something like
select a.id, count(b.id) from T1 a, T1 b where
a.id = b.parentid group by a.id;
Let me know if this works.
http://www.jasncab.com/huberblog :: Jason Huber
|
|
|
|
|
I migrate the microsoft class : SqlHelper to OracleHelper, i used in this class ODP .NET 10..
I need some person to do the test. if there is someone who is interested by this this request ,please send me an email
at nizar@myway.com and i will response him.
thnx
ENSI TUNISIA
|
|
|
|
|
we are developing an ASP.NET application with SqlServer at backend..
there are an supplier and about 3000 customer, and each customer has about 3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is bigger then sale amount, we are updating its stock, and we increment suppliers stock. The query is like that :
DECLARE @StockAmount int
SELECT @StockAmount = Amont FROM Stocks WHERE CustomerId = @BuyerCustomerId
IF @StockAmount > @SaleAmount
UPDATE Stocks SET amount = @StockAmount - @SaleAmount WHERE CustomerId = @BuyerCustomerId
SELECT @SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId = @SellerCustomerId
UPDATE Stocks SET amount = @StockAmount + @SaleAmount WHERE CustomerId = @SellerCustomerId
i know, each customer has got not so many users and a concurrency problem seems to be not a big possibility. but there is just a 1 supplier record and i think conlicts are possible. how can i alter this problem. after a research, i found
SELECT @SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE CustomerId = @SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction finishes. any approaches are appreciated. thanks.
|
|
|
|
|
Hi,
I want to execute a transact SQL like below
Declare @local as varchar(30)
Declare @LocalStr as varchar(50)
Set @LocalStr ='Select @local =ColumnName, * from tablename where Condition'
Exec(@LocalStr)
But it gives @local not declare error
or how can I assign the value of columnName to local variable, It should be Transact (Dynamic) as conditions are dynamic
Thanks in advance
|
|
|
|
|
|
|
Hi all,
In my .NET application i want to read the sql server database schema(Table names,triggers attached to tables etc).
Once user selects the database (sql server) to connect to, i will open the connection and show to user list of tables available in the database.
Is there any way of reading the database schema after opening connection to the database.
Thanks in advance,
Tushar Mahajan.
|
|
|
|
|
|
Thanks Colin it is returning all Table Names.
For triggers i am executing 'sp_helptrigger' stored procedure with table Name as
a parameter.It returns all the triggers and their types for that table.
Thanks,
Tushar Mahajan.
|
|
|
|
|
Hi,
i would like to know how it's possible to work with a output parameter.
i already worked this way for input:
"insert into ... (test, test2) values(@test, @test2)"
and then i add those parameters to the parameterscollection of my odbccommand object.
now i want to do a simple select of one column:
"select test from testtable" but how can i retrieve that value , the result of my query i mean, without working with a dataset or a reader. is it possible to also work with parameters for this?
grtz & thx
|
|
|
|
|
|
thx that works 
and what if i don't have more than one value, let's say:
select test, test2 from testtable
how do i get the value of test and test2 ? (without reader and without filling a dataset)
|
|
|
|
|
Hi, I have a remote query which i invoked using sp_ExecuteSQL. Using the SQL Profiler, I found out that the query is being executed 6 times instead of only ones. Does anyone know how to prevent this from happening? I am using ADO in VB6. Thanks.
SDE
|
|
|
|
|
I created a full text index who contain organizations and there propreties. I did a stored proc that find all the organization in the table that correspond to a spécific name. The problem is when I have a hyphen in the name, the proc get case sensitive only for the first letter.
example :
search : arc-en-ciel, it returns : Arc-en-ciel CPE and arc-en-ciel
if I serch for : arC-eN-cIeL, it returns : Arc-en-ciel CPE but not arc-en-ciel
and if I search for : Arc-En-Ciel, it returns : Arc-en-ciel CPE and arc-en-ciel
my stored proc looks like this :
CREATE procedure SearchOrganization
(
@Name varchar(1000) = null
)as
declare @WhereName varchar(1000)
if (@Name is null)
set @WhereName = ''
else
begin
--Do poutine to extract and build WhereName clause
SET @WhereName = @Name
if(@KeyWordsOperator = 1) -- 'and'
SET @WhereName = REPLACE(@WhereName, N',', N'") and formsof(inflectional,"') + N'")'
else
if(@KeyWordsOperator = 0) -- 'or'
SET @WhereName = REPLACE(@WhereName, N',', N'") or formsof(inflectional, "' ) + N'")'
if(@KeyWordsOperator = 1 or @KeyWordsOperator = 0) -- 'and' et 'or'
Set @WhereName = ' contains(oo.u_Namestrip, ''formsof(inflectional, "' +@WhereName + ''')'
else --'EXACT'
Set @WhereName = 'oo.u_name = ''' +@WhereName + ''''
If (@IncludeAnd = 1)
set @WhereName = @And + @WhereName
set @IncludeAnd = 1
end
I'm a beginner at SQL and this the code was created by a other person. I tried to find something on the net to help me but I found nothing.
Sorry for my english,
Nik
|
|
|
|
|
Hi
where i can find a 100% free sql server 2000 documentor tool?
Tanks.
|
|
|
|
|
CodeSmith
After downloading CodeSmith there are templates included with the installation that should take care of it. If not it should get you close as you can customize the templates. Great Tool!
http://www.ericjsmith.net/codesmith/
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I'm writing my first .Net app (old ASP/VBScript developer). Using VS2003 I set up a SQLConnection & SQLDataAdapter. I use a SQLCommand to fill a Dataset. I'm displaying the Dataset in a DataGrid that has Edit enabled. I'm trying to figure out how to use the generated Update command in the SQLDataAdapter in my Update event handler. I have all the code to pick up the values from all the cells, but all the examples I've seen for the SQL Update code create a new connection, adapter and hard-code an Update command, set all the command parameters and execute the command. The SQLDataAdapter already has a parameterized Update command. How do I reference the parameters of that Update command to set them to the values of my cells and execute the command?
|
|
|
|
|
i make a query on my database.
I want the image data inside a row.
I select it and i start to read data.
The problem is that sometimes there is no data inside, so if i try to read i get exception.
Can i check if there is data inside that field before start ot read?
That field doesn't support NULL default value.
i use Microsoft Access database .mdb and ADO.NET
|
|
|
|
|
Or you could just wrap your code to read that field in a Try/Catch block. If it fails, there wasn't anything there to read.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
i've created an application that register and make login the users.
When the user register himself enter the following texts:
name: Marco
password: project
Then if the user try to login with this value it get login either write Marco and mArCO, project and PROJECt
i use the following code:
string query = "SELECT * FROM Table WHERE Name = '" + textBoxName.Text + "' AND Password = '" + textBoxPassword.Text + "'";
It is not case sensitive, why?
|
|
|
|
|
Sounds like the collation sequence for your database is set to case insensitive. You can change the collation sequence on a column-by-column basis if you like, but I find that has many problems when comparing data between columns with different collation.
Once set, you cannot change the collation sequence of the database as a whole as it has to be set at creation time.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i could create a new database...
how can set the property that handle case-sensitive in query statment?
|
|
|
|
|
I have a question - I am trying to design a database - contacts - and I have seen many around but do not serve my purposes - so I started with my requirements:
The logical breakdown where:
- the adress stored could belong to an individual or company.
- the relationship of person to company could be one person can own multiple companies but one company could be the employer of many persons.
- Thus - the address could be the address of the company but also of the person's work address or the address could be the persons personal address (and have nothing to do with the company - say an unemployed alcoholic who is a good friend).
I am gong in circles - any suggestions? I have not even started into the phone table yet!
|
|
|
|
|
Sounds like you need some many-to-many joins
Okay - Let's try and put this in atomic bullet points.
* Address can belong to an individual
* Address can belong to a company
* Person can own 0, 1 or more companies
* Company can employ 0,1 or more persons
* Person can have a work address
* Person can have a home address
* A person can be employed by 0, 1 or more companies.
So, you have a person table, a company table and and address table. You also have some many-to-many joins which require intermediate tables, so you have a person_owns_company table and a company_employs_person table. These intermediate tables take the primary key from each side of the join and combine them together to make a compound primary key of its own.
So, if you have a PersonID and and CompanyID your person_owns_company table will have a compound primary key of PersonID and CompanyID. They also remain foreign keys back to their originating tables.
A person can have a homeAddressID column which will be a foreign key to the address table
A company can have an addressID column which, again, will be a foreign key for the address table.
You may wish to extend this futher as companies can have more than one location and the addressID may be better placed on a company_location table.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
