Forum Stats

  • 3,770,503 Users
  • 2,253,127 Discussions
  • 7,875,489 Comments

Discussions

All related records - take 2

Gary Perkins
Gary Perkins Member Posts: 24 Red Ribbon

Sorry for the 2nd post but my example data wasnt complete :(

Anyway the new version:

Hi Everyone,

I must be having a bad day :)

I have TABLE1 which contains a list of id's (in the real data they are authorisations) in REQUIRED_TABLE2 are dependant authorisations. I.E. If you need authorisation 2 then you must also have authorisation 3 and 5. Authorisation 1 doesnt have any other required authorisations so just return 1

So I need a query that returns all ids for each authorisation (id)

Pass in 1 and return

-1

Pass in 2 and return

-2

-3

-5

Thanks heaps

Gary


CREATE table "TABLE1" (

  "ID"     NUMBER,

  "NAME"    VARCHAR2(20),

  constraint "TABLE1_PK" primary key ("ID")

)

/


CREATE table "REQUIRED_TABLE2" (

  "TABLE1_ID"     NUMBER,

  "REQUIRED_TABLE1_ID" NUMBER

)

/

alter table "REQUIRED_TABLE2" add constraint "REQUIRED_TABLE2_PK" primary key ("TABLE1_ID","REQUIRED_TABLE1_ID")

/

ALTER TABLE "REQUIRED_TABLE2" ADD CONSTRAINT "REQUIRED_TABLE2_FK1" 

FOREIGN KEY ("TABLE1_ID")

REFERENCES "TABLE1" ("ID")

/

ALTER TABLE "REQUIRED_TABLE2" ADD CONSTRAINT "REQUIRED_TABLE2_FK" 

FOREIGN KEY ("REQUIRED_TABLE1_ID")

REFERENCES "TABLE1" ("ID")

/

BEGIN

insert into table1 (id, name) VALUES (1, 'One');

insert into table1 (id, name) VALUES (2, 'Two');

insert into table1 (id, name) VALUES (3, 'Three');

insert into table1 (id, name) VALUES (4, 'Four');

insert into table1 (id, name) VALUES (5, 'Five');


insert into required_table2 (table1_id, required_table1_id) VALUES (2, 3);

insert into required_table2 (table1_id, required_table1_id) VALUES (2, 5);

insert into required_table2 (table1_id, required_table1_id) VALUES (3, 4);

END;


CREATE table "TEST_AUTH" (

 "NAME"  VARCHAR2(20),

 "ID"   NUMBER -- this is the authorisation id the user needs and can be more than one

)

/

alter table "TEST_AUTH" add constraint "TEST_AUTH_PK" primary key ("NAME","ID")

/

ALTER TABLE "TEST_AUTH" ADD CONSTRAINT "TEST_AUTH_FK" 

FOREIGN KEY ("ID")

REFERENCES "TABLE1" ("ID")

/

So query for Fred and return

1

Joe and return

2

3

5

John

3

4


BEGIN

INSERT INTO test_auth (name, id) VALUES ('Fred', 1);

INSERT INTO test_auth (name, id) VALUES ('Joe', 2);

INSERT INTO test_auth (name, id) VALUES ('John', 3);

END;

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi,

    Please don't start multiple threads for the same question. It means you'll have to look in multiple places for answers, and any clarifications or corrections will have to be entered in multiple threads.

    Here's the latest answer from your original thread ( All related records — oracle-tech ):

    =============== Start of Copied Answer ==========

    Hi,

    Is this the output you want?

    ---------------------------
    So query for Fred and return
                 1
    Joe and return
                 2
                 3
                 5
    John
                 3
                 4
    

    with 9 rows, and 1 column?

    It would be simpler to have the text and the ids in separate columns, like this:

    TXT                                ID
    ------------------------------ ------
    So query for Fred and return        1
    Joe and return                      2
                                        3
                                        5
    John                                3
                                        4
    

    with either 6 or 9 rows.

    Simplest of all would be:

    NAME     ID
    -------- ------
    Fred     1
    Joe      2
    Joe      3
    Joe      5
    John     3
    John     4
    

    Would you accept something like that?

    In any case, join the test_auth table to the other tables.


    =============== End of Copied Answer ==========

    I'll mark the original thread as "Closed" so you only have to continue in this thread.

  • Gary Perkins
    Gary Perkins Member Posts: 24 Red Ribbon

    Thanks Frank and sorry for duplicate post I thought it would be cleaer since I sort of changed the question :)

    Anyway I'll add some more data to see if that helps:


    INSERT INTO test_auth (name, id) VALUES ('Bill', 2);

    We have a list of authorisations that can have dependant required authorisations and each person can have one or more authorisations. If they have an authorisation that has dependant ones they inherit the related authorisations (id's in the example data) Many people can (and will) have the same authorisation(s)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi,

    sorry for duplicate post I thought it would be cleaer since I sort of changed the question 

    That happens. Sometimes you realize the problem was mis-stated; sometimes the requirements change. One way to handle that is to add new replies to the original thread. A better way to handle that situation is to start a new thread, copying relevant parts of the original thread. Either of those approaches is okay. What I'm saying is do one or the other - BUT NOT BOTH. You'll get better replies faster if you don't multiple threads for the same problem.