Database

16-Feb-10 2:32

Good Day All

i have a Simple Query

select id,NodeID,Description,refParent from #nodes2

order by id ,refParent,description

this shows the following data

ID     NODEID	        DESCRIPTION           REFPARENT

======================================================================

0	149		Compulsory	        NULL

1	155		One of		         0

2	156		GunningM                 0

3	157		JonesJ		         1

4	158		One of		         0

5	159		D1127	                 4

if you can look in this table the Records ID 5 and 5 are fine but this record but

2	156		GunningM      	        0

is supposed to be under its parent which is the one with ID 0, so i want to sort it so that it can have the following results

ID     NODEID	        DESCRIPTION           REFPARENT

======================================================================

0	149		Compulsory	        NULL

2	156		GunningM                  0

1	155		One of		         0

3	157		JonesJ		         1

4	158		One of		         0

5	159		D1127	                 4

Thank you

Vuyiswa 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/

J4amieC16-Feb-10 2:38

J4amieC

16-Feb-10 2:38

Your question makes no sense whatsoever.

Why should record ID 2 come above record id 1? There is nothing there that could possibly identify the fact that it should do.

And besides, your order by gives prescendence to the ID column:

order by id ,refParent,description

So even if there was something (such as an ordinal column) it would be ignored.

If there is a fake order that children must come in, then you will need to add a column for this and order by that column.

Vimalsoft(Pty) Ltd16-Feb-10 2:45

16-Feb-10 2:45

The record

2	156		GunningM      	        0

is a Child of the Parent 0 and it has no Children. So there are other records that have REFPARENT of 0 but they have children. So if the record has no child it must come close to its parent.

Richard MacCutchan16-Feb-10 2:54

Richard MacCutchan

16-Feb-10 2:54

Your ordering is wrong, you are ordering by id first so your results are in the correct order. To get the results you want, you will need to change the "order by" settings; probably to refParent first, then by id descending.

txtspeak is the realm of 9 year old children, not developers. Christian Graus

Chris Meech16-Feb-10 3:07

Chris Meech

16-Feb-10 3:07

Can you please explain the criteria that cause the records with ID = 2 and ID = 1 to sort that way? Is it because there is no reference to that ID? Thanks.

Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Vimalsoft(Pty) Ltd16-Feb-10 3:25

16-Feb-10 3:25

let me Explain again. Here is my SQL Query that Produces that table till the end.
The First part that i run is this

Create table [#Nodes]
(id int IDENTITY(0,1),
 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 = 10 
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 =10

and it Produces the table #Nodes with the Following Records

ID  NODEID	PARENT                DESCRIPTION		REFPARENT
=========================================================================
0	149		NULL      	Compulsory		NULL
1	155		149	        One of			NULL
2	156		149		GunningM		NULL
3	157		155		JonesJ			NULL
4	158		149		One of			NULL
5	159		158		D1127			NULL

and in the next statement i create another temp table

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

and the temp table #nodes2 will look like this

ID     NODEID	       PARENT         DESCRIPTION	    REFPARENT
============================================================================
0	149		NULL	       Compulsory	      NULL
2	156		149		GunningM		0
1	155		149		One of			0
4	158		149		One of			0
3	157		155		JonesJ			1
5	159		158		D1127			4

and i made a mistake here by posting a query without a parent Field, to modify that it looks like this

select id as [id2],NodeID,parent,Description,refParent  
 from #nodes2
order by  nodeid,parent, refParent

dan!sh 16-Feb-10 6:13

dan!sh

16-Feb-10 6:13

That way you are ordering is not correct. Change the order by clause as per your preferences.

"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem

Jörgen Andersson16-Feb-10 9:06

Jörgen Andersson

16-Feb-10 9:06

If this had been Oracle, it would have been a walk in the park:

SELECT *
FROM nodes2
CONNECT BY PRIOR Id = RefParent
START WITH RefParent IS NULL
ORDER SIBLINGS BY Id

If you have SQLServer 2008, this query might work: (Havent tested)

WITH records AS
(
    SELECT
        Id,
        RefParent,
        CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY Id)) AS thePath
    FROM  nodes2
    WHERE RefParent IS NULL
        UNION ALL
    SELECT
        n.Id,
        n.RefParent,
        r.thePath + '.' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY n.Id)) AS thePath
    FROM records r
    JOIN nodes2 n ON n.RefParent = r.Id
)
SELECT *
FROM records
ORDER BY 
    thePath

There are times when I really hate Oracle, but when you're working with trees it really is SO superior to SqlServer

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.

Vimalsoft(Pty) Ltd16-Feb-10 19:14

Not sure if this should be in Linq or Database so forgive me. How would I do a left join in linq to sql in c#

16-Feb-10 19:14

Thank you for your Reply. Last night i slept over it and i came back with this

/****** Object:  StoredProcedure [dbo].[sp_Traverse_Tree_Special]    Script Date: 02/16/2010 22:54:46 ******/
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

Thanks

How

JustWorking16-Feb-10 1:57

JustWorking

16-Feb-10 1:57

How to select duplicate names in SQL?

Re: How

Jörgen Andersson16-Feb-10 2:21

Jörgen Andersson

16-Feb-10 2:21

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.

Re: How

JustWorking16-Feb-10 19:49

JustWorking

16-Feb-10 19:49

Can we use HAVING without GROUPING

Re: How

Jörgen Andersson16-Feb-10 21:18

Jörgen Andersson

16-Feb-10 21:18

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.

tonyonlinux15-Feb-10 9:12

tonyonlinux

15-Feb-10 9:12

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 Frown | :(

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. Confused | :confused:

Re: Not sure if this should be in Linq or Database so forgive me. How would I do a left join in linq to sql in c#

Pranay Rana15-Feb-10 19:22

Pranay Rana

15-Feb-10 19:22

I think this issue of left outer join so go through below link once may be this will help you

http://www.devsource.com/c/a/Languages/Defining-a-Left-Join-with-LINQ-Queries/1/[^]

sql server express installation

Yulianto.14-Feb-10 22:38

Yulianto.

14-Feb-10 22:38

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?

<italic>Don't work hard, but work effectively.
Travel guide | invest money

Not Active15-Feb-10 1:38

Not Active

15-Feb-10 1:38

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

http://msdn.microsoft.com/en-us/library/cc645993.aspx[^]

εїзεїзεїз16-Feb-10 1:41

εїзεїзεїз

16-Feb-10 1:41

Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Not Active16-Feb-10 6:32

Not Active

16-Feb-10 6:32

And your point is?

I know the language. I've read a book. - _Madmatt

εїзεїзεїз16-Feb-10 6:58

εїзεїзεїз

16-Feb-10 6:58

2 C things from others point of view.

Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Not Active16-Feb-10 7:10

Not Active

16-Feb-10 7:10

Again, your point is?

I know the language. I've read a book. - _Madmatt