Select query to return parent rows

[Balaji Krishnan]

I need to get the super set(parent) record from a relational table. Following is the structure of the table,

Code: Select all

    create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),
    primary key (data_field1,data_field2,data_field3) );

    insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
    insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
    insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
    insert into relation (data_field1, data_field2, data_field3) values (400,123,334);


    insert into relation (data_field1, data_field2, data_field3) values (345,-10,500);
    insert into relation (data_field1, data_field2, data_field3) values (345,210,500);
    insert into relation (data_field1, data_field2, data_field3) values (345,301,500);
    insert into relation (data_field1, data_field2, data_field3) values (345,403,500);
    insert into relation (data_field1, data_field2, data_field3) values (345,504,500);
     
    insert into relation (data_field1, data_field2, data_field3) values (521,550,-10);
    insert into relation (data_field1, data_field2, data_field3) values (521,550,125);
    insert into relation (data_field1, data_field2, data_field3) values (521,550,254);

     
    insert into relation (data_field1, data_field2, data_field3) values (500,110,350);
    insert into relation (data_field1, data_field2, data_field3) values (320,145,654);
    insert into relation (data_field1, data_field2, data_field3) values (214,102,103);


Assume the data value '-10' as the parent for each column. So the output should be like below with super set rows only

Code: Select all

DATA_FIELD1            DATA_FIELD2            DATA_FIELD3           
---------------------- ---------------------- ----------------------
-10                            123                    334                   
345                            -10                    500
521                            550                    -10
500                            110                    350
320                            145                    654
214                            102                    103               



Please help which sql function i have to use to get the desired output

[Tim...]

Hi.

I don't understand what "-2" has got to do with anything. There is no value like that in the table and you've not explained what it is used for, so not really sure what I can say.

In addition, some of the insert statements reference a column that is not present in the table, so this doesn't fill me with confidence that you've actually tested your setup script.

Cheers

Tim...

[Balaji Krishnan]

Hi Tim,
Sorry for the confusion. I have modified the original post. Here '-10' will be considered as parent(super set) for all other values. If i have records like below,

Code: Select all

-10   200  300
100   200  300
123   200  300
521  201  300


Then my output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).

Code: Select all

-10   200  300
521  201  300


Hope this is clear.

[Tim...]

Hi.

You've still not explained the relevance of this "-10" value. There are three rows in your data that contain -10, so it it is just the presence of the value in a row, then I don't understand what you are asking for. You talk about "parent", but you have not explained what you mean by this term. No clue what you are asking for here.

Cheers

Tim...

[Balaji Krishnan]

Tim,

Sorry for the delayed response. Here '-10' will be considered as the root of other values present in that column. If i have a three records below,

Code: Select all

    -10   200  300
    100   200  300
    123   200  300


First row of the first coulmn has '-10' (root). Next two rows has identical values in 2nd and 3rd columns only has different values at first coulmn when compared with 1st row. Now i have to prepare a query which should return only the first row here.Hope i am clear here.

[Tim...]

Hi.

I really don't think you have said what you mean. What your last paragraph has asked for is this.

Code: Select all

SELECT * FROM relation WHERE data_field1 = -10;

I doubt this is what you really want, but it is what that paragraph has asked for.

If you can't ask a clear question, you are not going to get a good answer.

Cheers

Tim...

[Balaji Krishnan]

Tim,

If we have two more rows in the above list

Code: Select all

        -10   200  300
        100   200  300
        123   200  300
        124   214  125
        247   587   323


Output should return three rows mentioned below,

Code: Select all

        -10   200  300
        124   214  125
        247   587   323


Fisrt row is super set of rows 2 & 3 in the list, because data_field1 value is '-10' and data_field2 , data_field2 are identical. Row 4 & 5 in the list are not matched to super set row, hence it should be presented in the query output.

[Balaji Krishnan]

Parent row - When a row has value '-10' in any of the coulmn (datafield1 or datafield2 or datafield3) and has a sub set row.
Example:
If a row has '-10' in datafield2 and values 50 and 100 in datafield1 and datafield3 respectively. And a another row has some value at datafield2 and has 50 and 100 for datafield1 and datafield3 then this row is said to be sub set of the parent row

Code: Select all

row1 : 50 -10 100
row2 : 50 21  100
row3 : 50 23  100


Here row1 is parent for row2 and row3. so select query should return row1 alone in this case.
output:

Code: Select all

row1 : 50 -10 100


If we have one more row in the above list like below

Code: Select all

row1 : 50 -10 100
row2 : 50 21  100
row3 : 50 23  100
row4 : 21 211 123


Here datafield1 and datafield3 are not identical to parent row (row1). Hence row4 should present in as a different one in the output.
output

Code: Select all

row1 : 50 -10 100
row4 : 21 211 123


[Tim...]

Hi.

I don't think I can help you because I don't understand what you want. In fact, I don't think you understand what you want because in five attempts to ask me for help, you have asked for 5 completely different things.

In one post you asked for this:

Code: Select all

select * from relation where data_field2 = -10;

DATA_FIELD1 DATA_FIELD2 DATA_FIELD3
----------- ----------- -----------
        345         -10         500

1 row selected.

SQL>

In another one you asked for this:

Code: Select all

select *
from   relation a where a.data_field1 = -10
union all
select b.*
from   (select * from relation where data_field1 = -10) a
       join relation b
         on a.data_field1 != b.data_field1
         and a.data_field2 = b.data_field2
         and a.data_field3 = b.data_field3
union all
select *
from   relation where data_field2 = -10
union all
select b.*
from   (select * from relation where data_field2 = -10) a
       join relation b
         on a.data_field2 != b.data_field2
         and a.data_field1 = b.data_field1
         and a.data_field3 = b.data_field3
union all
select *
from   relation where data_field3 = -10
union all
select b.*
from   (select * from relation where data_field3 = -10) a
       join relation b
         on a.data_field3 != b.data_field3
         and a.data_field1 = b.data_field1
         and a.data_field2 = b.data_field2;

DATA_FIELD1 DATA_FIELD2 DATA_FIELD3
----------- ----------- -----------
        -10         123         334
        200         123         334
        300         123         334
        400         123         334
        345         -10         500
        345         210         500
        345         301         500
        345         403         500
        345         504         500
        521         550         -10
        521         550         125

DATA_FIELD1 DATA_FIELD2 DATA_FIELD3
----------- ----------- -----------
        521         550         254

12 rows selected.

SQL>

If you can't actually decide and define what you want, it's not exactly easy for anyone else to help you. I think you need to sit back and actually think about what you want. Then write something and ask someone else in your office if they can understand your requirement. Only then, consider posting it. Being able to define a problem is a fundamental skill in I.T. Without it, you can't consider moving forward to actually coding something.

Cheers

Tim...

[Balaji Krishnan]

Tim,

Second query you have posted is what i really want. Thanks for your help

[Tim...]

Hi.

OK.

Cheers

Tim...