My Database is Oracle 10g.
I have say three tables.
MOTHER
CREATE TABLE MOTHER
(
ID VARCHAR2(10 BYTE),
PROC_DATE DATE,
THICK NUMBER,
WIDTH NUMBER
)
Data:
ID PROC_DATE THICK WIDTH
0001 01-Sep-2014 0.5 123
0002 01-Sep-2014 0.6 142
0003 01-Sep-2014 0.6 157
0004 01-Sep-2014 0.45 142
0005 01-Sep-2014 0.32 127
DAUGHTER1
CREATE TABLE DAUGHTER1
(
MOTHER_ID VARCHAR2(10 BYTE),
DAUGHTER_ID VARCHAR2(10 BYTE),
PROC_DATE DATE,
THICK NUMBER,
WIDTH NUMBER
)
Data:
MOTHER_ID DAUGHTER_ID PROC_DATE THICK WIDTH
0001 0011 01-Sep-2014 0.27 127
0001 0012 02-Sep-2014 0.27 127
0001 0013 02-Sep-2014 0.27 127
0002 0002 02-Sep-2014 0.6 134
0003 0031 02-Sep-2014 0.6 134
0003 0032 02-Sep-2014 0.6 134
DAUGHTER2
CREATE TABLE DAUGHTER2
(
MOTHER_ID VARCHAR2(10 BYTE),
DAUGHTER_ID VARCHAR2(10 BYTE),
PROC_DATE DATE,
THICK NUMBER,
WIDTH NUMBER
)
Data:
MOTHER_ID DAUGHTER_ID PROC_DATE THICK WIDTH
0002 0021 03-Sep-2014 0.6 134
0002 0022 03-Sep-2014 0.6 134
0002 0023 03-Sep-2014 0.6 134
0004 0041 03-Sep-2014 0.6 134
0004 0042 03-Sep-2014 0.6 134
0004 0043 03-Sep-2014 0.6 134
0031 0311 03-Sep-2014 0.6 134
0031 0312 03-Sep-2014 0.6 134
For simplicity I have ignored any constraint.
Logic for data in there tables is like this:
Some object with unique ID and other parameters are inserted in MOTHER table.
When this object is processed by some machines it may go to DAUGHTER1,DAUGHTER2... tables.
It is possible that Object is divided into more than one part in a Daughter table. So, if it is divided its ID will be
If Mother ID is 0001 then daughter's ID will be like 0011,0012,0013
If that object is undivided its ID will remain same in the Daughter table.
It is possible that a Object is passed through all Daughter tables divided or undivided.
It is possible that a Object is directly passed in Daughter2 or Daughter3 table divided or undivided.
I have to prepare a query which gives object details from mother to all daughter table i.e., Mother and its corresponding daughter details in
a single query. My required output is like this:
The above example is very simple one and I have not considered actual values in Thick, Width etc. Only important thing is Object ID and how it is generated in if it is divided into daughter ID.
One more thing if a Obejct is entered with same Mother and Daughter ID in the same table then I need to fetch data with MAX Date.
In the desired output I have taken few Objects not all objects bucause I think it is sufficient for example. If any more clarification is needed please reply.