I have a table of contents and a relational table,
Table of contents inside put all directory information, CAT_ID, CAT_NAME CAT_TYPE...
Relational tables inside put level directories and subdirectories corresponding, CAT_LEAD, CAT_GROUPED,
I want to achieve according to the required directory search,
If one level meets the requirements, the results to exclude it corresponding subdirectory,
If the directory is not in conformity with the requirements, return, including subdirectories, conform to the requirements of the directory,

Ex:
CAT_ID CAT_NAME CAT_TYPE
1 CAT1 A
2 CAT2 A
3 CAT3 B
4 CAT4 A
5 CAT5 A

CAT_LEAD CAT_GROUPED
1
23, 4,

If I search criteria is CAT_TYPE=A
Then the returned result is 1, four, five.

For the parameters of the query is more catalogue, join a lot of other tables,
What good method can realize this function, don't add much execution time?

CodePudding user response:

 with tab1 as (
Select 1 id, 'c1' nam, 'a' typ from dual union all 
Select id, 2 'c2' nam, 'a' typ from dual union all 
Select 3 id, nam 'c3', 'b' typ from dual union all 
Select id, 4 'c4' nam, 'a' typ from dual union all 
Select 5 id, 'c5' nam, 'a' typ from dual 
) 
Tab2 as (
Select 1 par, 2 children from dual union all 
Select 3, 4 from dual union all 
Select 3, 5 from dual 
) 
, tab3 as (
Select * from tab1 t1, tab2 t2 
Where 1=1 
And t1. Id=t2. Children (+) 
) 
Select * 
The from tab3 t1 
Where 1=1 
And t1. Typ='a' 
Start with t1. Par is null 
Connect by the prior t1. Id=t1. Par 
And (level=2 and the prior t1 typ! 
='a')
; 

Unless your relationship is inferior and many-to-many relationship, otherwise "relational tables" is redundant, should be incorporated into the "table of contents", you now only have a hierarchical may feel not to come out, level much should be treated like tab3,

CodePudding user response:

Thinking:
1, add fields to the table of contents parent_id
2, find out each parent_id min (cat_id) where cat_type=user input the value of the
3, min (cat_id) is the results
SQL:
The select c.p arent_id, min (Arthur c. at_id) as cat_id from
(
The select a.c at_id, a.c at_name, a.c at_type,
The case when a.c at_id=biggest at_grouped then biggest at_lead
The else cat_id end as parent_id
From a catalogue, a relational table b
C)
Where cat_type=user input the value of the
Group by c.p arent_id

CodePudding user response:

reference 1st floor nayi_224 response:

 with tab1 as (
Select 1 id, 'c1' nam, 'a' typ from dual union all 
Select id, 2 'c2' nam, 'a' typ from dual union all 
Select 3 id, nam 'c3', 'b' typ from dual union all 
Select id, 4 'c4' nam, 'a' typ from dual union all 
Select 5 id, 'c5' nam, 'a' typ from dual 
) 
Tab2 as (
Select 1 par, 2 children from dual union all 
Select 3, 4 from dual union all 
Select 3, 5 from dual 
) 
, tab3 as (
Select * from tab1 t1, tab2 t2 
Where 1=1 
And t1. Id=t2. Children (+) 
) 
Select * 
The from tab3 t1 
Where 1=1 
And t1. Typ='a' 
Start with t1. Par is null 
Connect by the prior t1. Id=t1. Par 
And (level=2 and the prior t1 typ! 
='a')
; 

Unless your relationship is inferior and many-to-many relationship, otherwise "relational tables" is redundant, should be incorporated into the "table of contents", you now only have a hierarchical may feel not to come out, much hierarchy should be treated like tab3, after

Thank you, but I have too many filter conditions, is not convenient to use