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 Go to next message
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 #659320 is a reply to message #659319] Thu, 12 January 2017 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Do you realize that your post is unreadable just due the superfluous blank lines.
If you don't take any effort to post a correct question, you should think we don't spend (waste) any time to help you to solve it.

Re: Help to write the SQL query to [message #659321 is a reply to message #659320] Thu, 12 January 2017 12:32 Go to previous messageGo to next message
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
Re: Help to write the SQL query to [message #659322 is a reply to message #659321] Thu, 12 January 2017 12:52 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is a school homework assignment, isn't it. You need to show what SQL you have tried so far, and PLEASE use copy/paste from SQL*Plus with [code] tags when you do this.
Previous Topic: DBA_VIEWS giving ORA-00942 Error
Next Topic: ORA-14155
Goto Forum:
  


Current Time: Fri Mar 29 05:46:51 CDT 2024