|
Thank you for your Reply. Last night i slept over it and i came back with this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Traverse_Tree_Special] @curr int
with recompile
AS
set nocount on
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes]'))
drop table [#Nodes]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes_FINAL]'))
drop table [#Nodes_FINAL]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#SemiFinal]'))
drop table [#SemiFinal]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Surt1]'))
drop table [#Surt1]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Children]'))
drop table [#Children]
Create table [#Nodes]
(id int IDENTITY(0,1),
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
Create table [#Nodes_FINAL]
(id int IDENTITY(0,1),
id2 int ,
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
INSERT INTO #Nodes
(NodeID, Parent, [Description])
select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description]
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of')
where n.curr = @curr
union
select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject'
inner join tbl_modl m on m.id = n.modl
where n.curr = @curr
select distinct nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent
into #nodes2 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.Description
WITH CTENodes AS
(
SELECT
ID,
NODEID,
PARENT,
DESCRIPTION,
REFPARENT,
CAST(ROW_NUMBER() OVER(ORDER BY id ) AS VARCHAR(MAX)) NodePath
FROM #nodes2
WHERE REFPARENT is null
UNION ALL
SELECT
c.ID,
c.NODEID,
c.PARENT,
c.DESCRIPTION,
c.REFPARENT,
NodePath + '.' + CAST(ROW_NUMBER() OVER(ORDER BY C.id ) AS VARCHAR(MAX)) NodePath
FROM CTENodes AS P
JOIN #nodes2 AS C
ON C.REFPARENT = P.id
)
SELECT ID,NODEID, PARENT, DESCRIPTION, REFPARENT
into #SemiFinal
FROM CTENodes
ORDER BY NodePath,ID,REFPARENT
--These are all Records that inherit from the Root
select distinct s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Surt1 from #SemiFinal s1
inner join #SemiFinal s2
on s1.id = s2.id
where s1.RefParent = 0
and s1.id != s1.Refparent
and s1.Parent in (select Nodeid from #semifinal where Parent is null and ID = 0)
--Find Records that are Children
select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Children from #SemiFinal s1
where Refparent <> 0
--Get the Record that does not have Children
--select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent from #Surt1 s1
--where s1.id not in (select refparent from #Children)
--delete those that have Children from the #surf1 table
delete #Surt1
from #Surt1 s1
where s1.id in (select refparent from #Children)
--Then Union the Results the way you want them
--First Union the Root
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is null
--Follow with the Children
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #Surt1
--Follow with the Normal
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID, nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is not null
and ID not in (select ID from #Surt1)
select distinct ID, nodeid , parent, [description] from #Nodes_FINAL
order by nP.id , nodeid
and it did the Trick
ThanksVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
How to select duplicate names in SQL?
|
|
|
|
|
SELECT *
FROM Table
HAVING Count(name) > 1
To get a better answer you'll need to specify your question better.My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
Can we use HAVING without GROUPING
|
|
|
|
|
No,
Sorry about that.My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
Here's My issue:
I have 4 Tables
Author BookAuthor
--------- --------------
ID BookID
AuthorFirst AuthorID
AuthorLast
Book BookNumber
--------- --------------
ID ID
Title BookID
Price SurrogateNumber
Retired
Keywords
author.id linked to bookauthor.authorid
book.id linked to bookauthor.bookid
book.id linked to booknumber.bookid
I don't understand how to get all my records that have instances in BookNumber. Basically what is happening is I have 94 rows so far in booknumber (which is what i want), but since booknumber contains duplicate bookid's i only get a totaly of 86 rows back when i do the following:
var options = new DataLoadOptions();
options.LoadWith<Book>(c => c.BookAuthors);
options.LoadWith<BookAuthor>(c => c.Author);
options.LoadWith<Book>(c => c.BookNumbers);
db.LoadOptions = options;
books = db.books.tolist();
bookbindingsource.datasource = books;
and whenever I look in the database 86 books are in books (so that is right) because i have duplicates that were added to booknumber to make it the total of 94.
So my question is how do i get all 94 books instead of just the 86?
I tried this but its obviously wrong because I got like 116 results
var RightJoin = from b in db.Books
join ba in db.BookAuthors on b.ID equals ba.BookID
join a in db.Authors on ba.AuthorID equals a.ID
join bn in db.BookNumbers on b.ID equals bn.BookID into bookandBook
from bn in bookandBook.DefaultIfEmpty()
select b;
bookBindingSource.DataSource = RightJoin.ToList();
I just want to be able to show every single book I have entered in the database where it has a unique surrogate number. In the current case only 94 books...
thanks for your help.
|
|
|
|
|
|
I'm setting my sql server express for LAN. I see that there are so many things to setup. Isn't there an easy way to do this? Maybe from the installation?
|
|
|
|
|
Yulianto. wrote: sql server express for LAN
Didn't know there was an edtion specific for LAN.
Yulianto. wrote: Isn't there an easy way to do this?
Yes, run the setup. I know the language. I've read a book. - _Madmatt
|
|
|
|
|
|
And your point is? I know the language. I've read a book. - _Madmatt
|
|
|
|
|
2 C things from others point of view. Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Again, your point is? I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Depends , whats your security level and what you are going to install.. Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Hi. I came to software development primarily through physics. I have a fair amount of experience, but not much of it has to do with databases. I'm building an exercise database for a web site, and I need some suggestions about schema.
The idea is to relate exercises to muscles. This, however, is overly simplistic. Any exercise can belong to a group of exercises, and there can be multiple variations of an exercise. For example, front squats and back squats are fundamentally different exercises, even though both are squats. Likewise, when doing a back squat, someone can go down until thighs are parallel, or go down until his rear is almost touching the ground. These are both back squats, but have quite different effects.
My initial thought was to have a table for exercise groups, exercises, and exercise variations. Along the same lines I would have a table for a muscle group, and another for muscles. All would have mutually exclusive unique id's. This way, in a single relational table (many to many relationship), a given row could have one field containing a muscle or a muscle group, and another field can contain an exercise group, and exercise, or an exercise variation.
Although this would be well normalised, I'm thinking that once I carry this on to a few other things (like heads, joints, etc) this could result in a lot of small tables. It is also somewhat limiting. What if I want a variation of a variation, for example?
My second idea was that muscles and muscle groups would be in one table. There would be a field called 'group'. 'vastus lateralis' would be related to 'quadriceps'. For 'quadriceps', this field would be blank. Likewise, in the exercise table, I would include exercises, exercise groups, and exercise variations. Then under the 'variation of' field, 'parallel squat' would be related to 'back squat', 'back squat' would be related to 'squat', and for 'squat' the field would be empty. This seems to me to be a more flexible approach, and would probably be easier to maintain, but like I said, I do not have the benefit of experience on this. Can anyone give me advice on this?
Thanks
Matt Brown
|
|
|
|
|
Hy,
I have something like this:
SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);
The dilema is that
ORDER BY Defects does not work
and
ORDER BY SUM(QualityData.NrOfDefects);
Is there a better way?
Thanks
|
|
|
|
|
SELECT Code, Defects from
(SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code)
ORDER BY Defects;
|
|
|
|
|
|
OK. I did not have access to much resources when I answered your question. I assumed that you had problems with your original queries. However, here I have access to my environment, and I tested your query
SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);
It works with no problem. It works with no problem if I use
ORDER BY Defects
So just ignore my "solution". There must be something else in your problem.
My test environment is MySQL version 5.
|
|
|
|
|
It's all fixed now.
I now know for a fact(tested) that even the
ORDER BY SUM(QualityData.NrOfDefects);
does not cause a recalculation.
|
|
|
|
|
Your initial query is fine; it will not require a second set of calculations for the sort. To verify this, run the query in Management Studio and look at the Actual Execution Plan.
|
|
|
|
|
Hello guys.....title says it all. How can we make a database of music and access it using vb.net or C#.net??
|
|
|
|
|
Buy a Database book or copy one of these:
Google[^]I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
This is not what I was talking about. Lets suppose that i wanna make my own media player. In this case, I will need to access the music from my own hard disk and make a playlist.....
|
|
|
|
|
Muzammil Saeed wrote: This is not what I was talking about.
Your title says "Music Database", and your first entry states "Hello guys.....title says it all.", so which is it to be? If you have no interest in creating a music database then try and make your question clearer.
To make a playlist you need to scan all your music files, extracting the title, composer, orchestra etc information and building some sort of database which your media player can access. If you think a database is too much work you can use any alternative file type that you feel comfortable with.txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|