Criciel Logical Problem : How to solve ?

[RushiShroff]

Well I have parent child relationship between categories and subcategories in my master table.

Table : Application
Fields :

Application_Id
Application_Name
Application_Parent_Id

Here Appcation_Id indicates Id of a particular cat or sub category.

Name indiactes name and most important last one parent is indicates the parent ID of that sub category.

Like

1 Business 0
2 Technology 0
3 Accounting 0
4 IT 1
5 ERP 4

So first 3 are main categories.
Business has one sub cat IT and it has also one ERP.

Table : Product

Now I am inserting this ID in my transaction table Products.
To indicate which ID it has.

Like if my product is PeopleSoft I will insert 5 in the Field Application.

I am offering search also in the site.
There I am searching on the basis of first 2 categories and subcategories.So in first combo I select all categories having parent application ID 0(Main)

When user selects some main cat, after submitting page I list all sub categories for that category.(Parent 1 or selected)

In my mechanism,how can I perform search if I have selected
Business as main category and no choice for sub category ?

I want ERP to come as a result ?
Or it may be some sub branch of ERP also which is listed then I also want it to come as my result.

Think and answer ....!
Only experts please !

Rushi Shroff Rushi@emqube.com
"Life is beautiful."

 

[codestorm]

Not SURE I understand - are you after something like:

select t1.Application_Id, t1.Application_Name
from [Application] t1
inner join [Application] t2
on t1.Application_Id = t2.Application_Parent_Id
where t2.Application_Id = @givencategory

(haven't tested that..) codestorm
Fire bad. Tree pretty. - Buffy
Hey, I'm operating on a limited mental budget here.
<insert witticism here>

 

[RushiShroff]

Not satisfactory..

somebody please !! Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[RushiShroff]

Please !! Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[RushiShroff]

is there a geneous out here !!?? Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[RushiShroff]

No body alllas !!!!!!!!! Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[derren]

Rushi, you have caused yourself a headache here! After reading your thread for 20 minutes I think I know what you are doing. The problem is that you need to create a recursive function to iterate through your application table to find all occurrences of your search category, and this will begin to slow like a dog, if it can be done efficiently at all.

I think you need to look again at what you are putting into your transaction table to leave a clue as to what the hierarchy of the transaction's categories and subcategories are. For example you would put -0-1-4-5- in for a transaction in ERP, and -0-1- for a business transaction.

Then you can search based on looking for &quot;-num-&quot; or &quot;-num-&quot; or &quot;-num-&quot;. So a search for business would look for all transactions which contained &quot;-1-&quot; in the category field. This would return IT and ERP results as they too contain -1-. Using very rough code (because I can't remember the correct use of &quot;like&quot; (?))

Select * from transactions where field like %-1-

This should give you more joy. I am assuming that when you create your transaction you know which catgory and subcategories you are in - if you don't fix it so that you do.

Have fun Rushi Derren
[Mediocre talent - spread really thin]

 

[RushiShroff]

Thanks Derren,
I will forward this mail to my boss because he is from Database background and always tries new innovative ways to rather improve the quality of the software.

Yes I know iterating through a parent child relationship could be very hectic and will increse the load on the server CPU.That is why I posted the question here.

Also in Application master table I want to put 0-1 etc rather then absolute parent id.So when selected and inserted, the entire path will go to databse.

Also I can select multiple categories for a single product.
So in Software_Application table
[
Id
Product_Id
Application_Id
]
it will be easy to sort the records with help of LIKE operator.

Cheers Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;