|
Hi. I've a table Project(ProjectId, ParentProjectId, ProjectName)
the sample data are --------------------------
1,0, X
2,1,Y
3,2,Z
4,3,W
now for a given project id i want to get projectid, projectname for a given projectid.
e.g. i want 1,X for projectid = 4
i want to SQL query for this. plz help me.
|
|
|
|
|
So what have you tried? Anyone who has read more than 1 chapter of a book on SQL or has spent more than 10 minutes learning what to do can answer this. It is so basic that I suspect you tobe a troll.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To be fair, it's not quite that straightforward if you look at the data.
ProjectId ParentProject ProjectName
1 0 X
2 1 Y
3 2 Z
4 3 W
What he wants (I think) is to start from project 4, and go all the way back to the root parent. So, 4 is a child of 3 which is a child of 2 which is a child of 1 which has no parent. So, he wants to know that project 4 (name W) is part of the hierarchy ultimately descending from project 1 (name X).
The SQL to pick out the ultimate root parent when you don't know how deep the hierarchy might be is actually quite challenging. I'm not sure a beginner could write this. I'm not sure I could, to be honest.
|
|
|
|
|
I'm impressed, I did not get that from the question at all. And yes building the hierarchy stuff is quite challenging. I did an article on using it in C# but the SQL stuff I left out for this reason. It is also not well covered.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
First of all the question is unclear.
What I made out is that, you want to get the parent record of 4 i.e. 1
What if I pass the Projectid = 3 or 2?
You should give the relevant scenarios so that others can also make out.
Any way, based on what I understand, here is the solution
SELECT PROJECTID,PROJECTNAME
FROM PROJECT
WHERE PROJECTID = ( SELECT P1.PARENTPROJECTID + 1
FROM PROJECT P1
INNER JOIN PROJECT P2
ON P2.PROJECTID - <code>4</code> = P1.PARENTPROJECTID )
Hope this helps.
But as what I found that if for 4 the parent is 1, the same applies to 2 or 3 also.
In that case why not you are making a top count?
Let me know in case of any concern
Niladri Biswas
|
|
|
|
|
Take a look at this[^] article. If it does not actually solve your problem, it should at least give you some terms to Google.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Select @parentid=parentid from tablename where projectid=4
if(@parentid<>0)
Begin
WHILE (@PARENTID <> 0 )
BEGIN
Select @parentid=parentid from tablename where projectid=@parentid
if(@parentid is null)
break
set @id=@parentid
End
End
You can use these logic to get the parentid, Variable @id will contain your final parentid I had not tested this code so u may have to made some change in this.
It may get in infinite loop so i used
if(@parentid is null)
break
|
|
|
|
|
Now all of my stored procs are coming in with a ;1 at the end of them. I haven't seen them since the old VMS days...
It only does it on the CommandText of the tableadapters, and only on one of my machines. Both are using the same connection string...
Ok, so what setting did I change?
Thanks,
Nick
This are my own opinions. You know the rest.....
|
|
|
|
|
Nick Jacobs wrote: Ok, so what setting did I change?
Oh goody this is one of those guess the setting games, aaahhhhmmmm, nope I dunno what setting did you change. Bloody hell Nick how are we supposed to guess that?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This
Mycroft Holmes wrote: Bloody hell Nick how are we supposed to guess that?
My hope is somebody ran across the same problem. That's how we're suppose to "Guess" that one.
This are my own opinions. You know the rest.....
|
|
|
|
|
I want to get the values from a database table cells and set them as text for say four radiabuttons.
please help.
ML Lingwati
|
|
|
|
|
And what is your concrete problem? Reading from the database? Setting the text? Please be more specific.
|
|
|
|
|
SeMartens wrote: And what is your concrete problem? Reading from the database? Setting the text? Please be more specific
Both I guess
ML Lingwati
|
|
|
|
|
Take and look this.[^]
I Love T-SQL www.aktualiteti.com
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Experts
i Want To search fuzzy word search using where clause in sql server 2005
Thank u
|
|
|
|
|
Sharma Dinesh Kumar wrote: i Want To search fuzzy word search using where clause in sql server 2005
Thank u
Take a look at FTS in SQL (to search the "Full Text"), and decide your level of fuzziness. Then search for background information on SoundEx and Levenstein 
I are troll
|
|
|
|
|
this is a sample account statement generation for an e-banking software
Plz i want the accounts and customers details to appear even if no transactions exists.
am finding it difficult making that work..i have tried full outer joins and outer joins
any help?
code below
IF @type='RANGE'
BEGIN
print 'Query began'
SELECT (SELECT Openbal FROM Accounts WHERE SUBSTRING(AccNum,9,4)=@AccNum) AS openBal,(SELECT Name FROM ThisCompany) AS CompName,
(SELECT Branch FROM ThisCompany) AS Branch,
(SELECT StreetAdd FROM ThisCompany) AS StreetAddress,
(SELECT AddLine1 FROM ThisCompany) AS AddLine1,
(SELECT AddLine2 FROM ThisCompany) AS AddLine2,
@dt1 as Date1,@dt2 as date2,
(SELECT Tel1 FROM ThisCompany) AS Tel,
(SELECT Website FROM ThisCompany) AS Web,
SUBSTRING(Accounts.AccNum,9,4) AS CustNum,
Accounts.AccType, Accounts.Currency, Customers.FName+' '+
Customers.LName as FName, Customers.CorrespAdd,
Transactions.Dt, Transactions.Debit, Transactions.Credit, Transactions.TranDetails, Transactions.Bal
FROM Accounts FULL OUTER JOIN
Customers ON Accounts.CustNum = Customers.CustNum FULL OUTER JOIN
Transactions ON Accounts.AccNum = Transactions.AccNum
WHERE SUBSTRING(Accounts.AccNUM,9,4)=@AccNum
AND Transactions.Dt BETWEEN DATEADD(dd,1,@dt1) AND DATEADD(dd,1,@dt2)
ORDER BY Transactions.Dt ASC
RETURN
END
|
|
|
|
|
prubyholl wrote: Plz i want the accounts and customers details to appear even if no transactions exists.
Replace the "Where" keyword with "And".
The outer join fails because the where condtion executes after the completion of outer join. Hence you dont get any results.
Whereas when you use an "And" clause , the condition is evaluated while the execution of the outer join.
Also handle the null values gracefully when there is no transaction for a customer.
When you fail to plan, you are planning to fail.
|
|
|
|
|
|
I want to display one row at a time on a gridview from my database table, where i'll be selecting the row by its primary key randomly and some condition using sql server 2005.
please help.
ML Lingwati
|
|
|
|
|
Hi, if I have understood your problem, then you have a table say tblRecords
with key(int, PK) and Value(varchar).
Like
Key Value
-----------------
1 Value1
2 Value2
3 Value3
4 Value4
................
.................
[n] Value[n], where n = any positive number
Now if you fire the following query, it will select only 1 record randomly.
SELECT *
FROM dbo.tblRecords
WHERE [Key] = CAST(RAND()*10 AS INT)
Note- If you have values between 1 to 1000, then it will be RAND()*1000
hope this helps.
Please let me know in case of any concern
Niladri Biswas
|
|
|
|
|
Hey everyone,
I have this budget lines table of which I have the columns CODE, ITEM and AMOUNT.
When my CODE column has this:
CODE
------------------------------
1.1.1 >>>>>> Category 1.1.1
1.1.1.1
1.1.1.2
1.1.1.3
2.2.2 >>>>>> Category 2.2.2
2.2.2.1
2.2.2.2
2.2.2.3
How can I get the subtotals of the amount column of each category?? And if I'm doing it the wrong way "in terms of database design" please tell me how would you do it??
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You could do your sum based on the first 5 characters of the code but that would be a bad idea.
Alternatively, you could change your database structure a little and add a Category table, add a relationship to the budget lines table and only store the final part of the code in your budget lines table.
Category
ID Code
-- ----
1 1.1.1
2 2.2.2
etc
BudgetLine
CategoryCode SubCode
------------ -------
1 1 (gives 1.1.1.1)
1 2 (gives 1.1.1.2)
2 1 (gives 2.2.2.1)
2 2 (gives 2.2.2.2)
I'm sure that won't be perfect for what you need but hopefully it'll give you the right idea.
|
|
|
|
|
Thanks mate, I appreciate your idea and it could be just right but my requirements are more complicated than that, you see, I cant predict how far budget line codes can be nested, some might go deeper than just one level and some just dont branch at all.. Do you still have anymore tricks up your sleeve??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I'm not sure if it would work but you could store all of the items in one table and link them to each other, i.e.
ID Code ParentID
-- ---- --------
1 1 NULL
2 1 1 (gives 1.1)
3 1 2 (gives 1.1.1)
3 2 2 (gives 1.1.2)
Using that method you could go to as many or as few levels as required.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
