[notice88]

   select 
     * 
   from 
     SubCategory a1 
   where Parent = '20005' or a1.seqnum in (select a2.Seqnum from SubCategory a2 where Parent = a1.SubCategoryID)

I need to get the parent and child.. there is a parent child relation ship in here where SubcategoryID is used to determine which is the parent of the subcategory.. But im only getting the parent

[mojo666]

Are you sure the parent and children really have the same seqnum? If so, I think this should work.

select  
   /*a1.Fields*/   
 from  
   SubCategory a1
   LEFT JOIN SubCategory a2
   ON a1.seqnum=a2.seqnum AND a2.Parent=a1.SubCategoryID
 where Parent = '20005' or a2.seqnum IS NOT NULL

If not just delete "a1.seqnum=a2.seqnum AND"

[e_i_pi]

I think mojo666 is close, but it might need some tweaking if I understand the schema correctly. Try this:

select  
   /*a1.Fields*/   
from SubCategory a1
LEFT JOIN SubCategory a2
	ON a1.seqnum = a2.seqnum
	AND a2.Parent = a1.SubCategoryID
where a1.SubCategoryID = '20005'

I'm iffy about the or a2.seqnum IS NOT NULL clause. I don't think that's actually doing anything, other than increasing the execution plan. The reasoning behind removing this is, if the value is not null, then it follows that a1.SubCategoryID = '20005'.

This post has been edited by e_i_pi: 03 October 2012 - 05:59 PM

[mojo666]

Well, based on the OP's query, he is trying to select all rows where a1.Parent='20005' or rows that have a match between parent and subcategoryID. The "or a2.seqnum IS NOT NULL" fulfills the second part. Perhaps the OP can clear up what he is looking for? notice88, can you provide some examples of the data and what you want the output to look like?

[e_i_pi]

Yes, but if a2.Parent = a1.SubCategoryID and a1.SubCategoryID = '20005', then it has to be the case that a1.seqnum = a2.seqnum. NULLs can't be used in equivalence operations in SQL (you use IS NULL, not = NULL) the only way a1.seqnum = a2.seqnum will evaluate to true is if both values are not null anyway.

[mojo666]

You're overlooking a couple of things. First of all, we used a "LEFT JOIN". This means all rows in a1 will be present even if there is no matching records in a2. As a result, all the a2 fields in such rows will be NULL. Thus, if a record has a1.Parent of '20005' it should be included in the results regardless of the a2 fields. However, if a1.Parent is not '20005' then the row should only be included in the results if there was a match found from the join. We know a match was found if a relevant a2 field "IS NOT NULL". If instead it "IS NULL" it will be excluded. The statement that produces these desired results after the join is "where a1.Parent = '20005' OR a2.seqnum IS NOT NULL".

EDIT - I emphasized "a1." because it was missing from my original post and for some reason I cannot fix that post.

This post has been edited by mojo666: 04 October 2012 - 03:05 PM

[e_i_pi]

This is all true, but I think you're getting caught up on his original query, which I believe itself is wrong. In a hierarchical table, the data is generally stored like this:
Hierarchy

ID
ParentID
<other fields>

Then, to query this data and get just the children of a node, you use this:

SELECT
  <fields>
FROM Hierarchy AS ParentNode
LEFT OUTER JOIN Hierarchy AS ChildNodes ON ChildNodes.ParentID = ParentNode.ID

Adding in the WHERE ParentNode.ID = '20005' simply selects a particular node of the tree, rather than return all children of all nodes. The seqnum itself is irrelevant in the clause, since it is not explicitly matched against, only implicitly via the LEFT JOIN. If you were matching against a particular seqnum, then it would be necessary to include it in the WHERE clause, but the OP isn't matching a seqnum, just a branch.

This post has been edited by e_i_pi: 04 October 2012 - 04:18 PM

[notice88]

mojo666, on 03 October 2012 - 02:44 PM, said:

Are you sure the parent and children really have the same seqnum? If so, I think this should work.

select  
   /*a1.Fields*/   
 from  
   SubCategory a1
   LEFT JOIN SubCategory a2
   ON a1.seqnum=a2.seqnum AND a2.Parent=a1.SubCategoryID
 where Parent = '20005' or a2.seqnum IS NOT NULL

If not just delete "a1.seqnum=a2.seqnum AND"

It returns only the parent.. hmmn. Im Using only 1 table where there is a field for ID and parent where It contains the ID to link which is the parent. Sorry I can't explain it clearly but this gives me some idea

e_i_pi, on 03 October 2012 - 05:58 PM, said:

I think mojo666 is close, but it might need some tweaking if I understand the schema correctly. Try this:

select  
   /*a1.Fields*/   
from SubCategory a1
LEFT JOIN SubCategory a2
	ON a1.seqnum = a2.seqnum
	AND a2.Parent = a1.SubCategoryID
where a1.SubCategoryID = '20005'

I'm iffy about the or a2.seqnum IS NOT NULL clause. I don't think that's actually doing anything, other than increasing the execution plan. The reasoning behind removing this is, if the value is not null, then it follows that a1.SubCategoryID = '20005'.

It returns only the SubCategory which has a seqnum of 20005.. But thanks for answering. I Appreciate it

This post has been edited by notice88: 07 October 2012 - 10:31 PM

[notice88]

e_i_pi, on 04 October 2012 - 04:17 PM, said:

This is all true, but I think you're getting caught up on his original query, which I believe itself is wrong. In a hierarchical table, the data is generally stored like this:
Hierarchy

ID
ParentID
<other fields>

Then, to query this data and get just the children of a node, you use this:

SELECT
  <fields>
FROM Hierarchy AS ParentNode
LEFT OUTER JOIN Hierarchy AS ChildNodes ON ChildNodes.ParentID = ParentNode.ID

Adding in the WHERE ParentNode.ID = '20005' simply selects a particular node of the tree, rather than return all children of all nodes. The seqnum itself is irrelevant in the clause, since it is not explicitly matched against, only implicitly via the LEFT JOIN. If you were matching against a particular seqnum, then it would be necessary to include it in the WHERE clause, but the OP isn't matching a seqnum, just a branch.

Thanks for Answering. hmmn.

I think im near here but the fields of the parents are empty. But thanks here is what I did

SELECT

  ChildNodes.Parent,
  ChildNodes.SubCategoryDesc
  
FROM SubCategory AS ParentNode

inner join SubCategory AS ChildNodes ON ChildNodes.Parent = ParentNode.SubCategoryID where 

ParentNode.Parent='20005' or ChildNodes.SubCategoryID in (Select SubCategoryID from SubCategory where Parent = '20005')

it returns all the child and parent, but parent fields are empty. thanks to all

[e_i_pi]

Try this then:

select  
   /*a1.Fields*/   
from SubCategory a1
LEFT JOIN SubCategory a2
	ON a1.seqnum = a2.seqnum
	AND a2.Parent = a1.SubCategoryID
where a1.SubCategoryID = '20005'
or a1.Parent = '20005'

[notice88]

e_i_pi, on 08 October 2012 - 03:25 PM, said:

Try this then:

select  
   /*a1.Fields*/   
from SubCategory a1
LEFT JOIN SubCategory a2
	ON a1.seqnum = a2.seqnum
	AND a2.Parent = a1.SubCategoryID
where a1.SubCategoryID = '20005'
or a1.Parent = '20005'

It returns only the parent. thanks. maybe something wrong with my db. ehehe.

[e_i_pi]

If you could post the structure of the Subcategory table, and how the table relates to itself, it would be easier to figure out. Self-referencing hierarchy tables shouldn't be too complex, they're pretty easy to set up and query against. I wrote a tutorial on it here, which you might be able to use to your advantage.