Home » SQL & PL/SQL » SQL & PL/SQL » Help to write the SQL query to (Oracle DEveloper 11G)
Help to write the SQL query to [message #659319] |
Thu, 12 January 2017 12:09 |
|
nirmeshvats
Messages: 2 Registered: January 2017
|
Junior Member |
|
|
Hello,
I need you help to write an query which return the record set from multiple table based on some parent / child and reference relation. We are using Oracle 11g only.
we're primarily looking to return sets of related records, excluding an entire set if one record in that set meets one of our constraints.
The core tables and relevant columns are as follows:
pr
*id
*parent_id
*root_parent_id
*is_closed
pr_addtl_data
*pr_id
*n_value
data_fields
* id
*field_class
Data
PR Table
The primary table is PR, which has all of the records, their parents (PARENT_ID) and root parent (ROOT_PARENT_ID).
The records in the PR table are related as a "family", with each id representing a record, the parent_id representing its parent record, and the root_parent_id being the top level of the tree, with all records in a family having the same root_parent_id.
Any record can reference another record via the following relationship:
select pr_id, n_value as ref_rec
from pr_addtl_data, data_fields
where pr_addtl_data.data_field_id = data_fields.id
and data_field.field_class = 21 and pr_id = :prid;
//In this case n_value is the record being referenced by the initial record (pr_id)
So, for our purposes we need to run a check in both directions. That is, given an initial record number, we'd need to check for any records it is referencing, as in the above example, or any records referencing it, as follows:
select n_value, pr_id as ref_rec
from pr_addtl_data, data_fields
where pr_addtl_data.data_field_id = data_fields.id
and data_field.field_class = 21 and n_value= :prid;
I'll simplify the example first, by discussing an input of one record, although the query we need will have to be a range (from pr.id to pr.id). As example from the provided database, if we were given a PR.ID=1067, we would find that all records with the same PR.ROOT_PARENT_ID are 1067, 1068, and 1069 (here the numbers are continuous, but they need not be). We would also find that 1067 references 1097. 1097 is in a family of 1097,1098,1099. 1099 is referenced by 1127, which is in the family of records 1127,1128,1129. And so on. We end up with a total of about 21 records in the set.
the result should be
1067
1068
1069
1097
1098
1099
1127
1128
1129
1247
1248
1249
1157
1158
1159
1187
1188
1189
1217
1218
1219
The real key here that we need to provide a "from" PR.ID and a "to" PR.ID. Then, for every PR in that range (inclusive), we need to run the check outlined above, to find the entire record set, excluding a set that contains a record that meets one of our constraint criteria. The result of our query should just be a list of all of the PR.IDs that aren't in a set that meets one of these exclusion criteria.
We need the pr.id only. Please let me know if you have any question.
Regard's
Nirmesh
|
|
|
|
Re: Help to write the SQL query to [message #659321 is a reply to message #659320] |
Thu, 12 January 2017 12:32 |
|
nirmeshvats
Messages: 2 Registered: January 2017
|
Junior Member |
|
|
Hi Michel,
We are using Oracle 11g Release 11.2.0.2.0.
Please use the below scripts to create the query:
CREATE TABLE "PR" ("ID" NUMBER(12, 0) NOT NULL ENABLE, "PARENT_ID" NUMBER(12, 0), "ROOT_PARENT_ID" NUMBER(12, 0));
insert into PR values(1067, null, 1067);
insert into PR values(1068, 1067, 1067);
insert into PR values(1069, 1068, 1067);
insert into PR values(1097, null, 1097);
insert into PR values(1098, 1097, 1097);
insert into PR values(1099, 1098, 1097);
insert into PR values(1000, 1099, 1097);
insert into PR values(1128, null, 1128);
insert into PR values(1129, 1128, 1128);
create table pr_addtl_data ( pr_id number(12), n_value number(12));
insert into pr_addtl_data values ( 1097, 1067);
insert into pr_addtl_data values ( 1128, 1097);
Actually one PR.ID can have more than one pr_addtl_data.n_vale(reference value) and that ref value can have PR.Id. and we have to check the reference for those pr.id as well. result should return the hierarchical data only 1067 then 1097 and then 1128 data(pr.id) as i mention in result above. only first 21 records required.
Please let me know if you have any question.
Regard's
Nirmesh
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 00:01:36 CDT 2024
|