Forum Stats

  • 3,770,483 Users
  • 2,253,122 Discussions
  • 7,875,482 Comments

Discussions

All related records

Gary Perkins
Gary Perkins Member Posts: 24 Red Ribbon
edited Jun 10, 2021 9:07PM in SQL & PL/SQL

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;


Actually I may have over simplified my example.

This table is a list of names that can have one or more authorisation id's

If we need to return all id's for a name I dont know how to put the where clause into thre UNION table?


CREATE table "TEST_AUTH" (

 "NAME"  VARCHAR2(20),

 "ID"   NUMBER

)

/


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:

Best Answer

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

    Hi, @Gary Perkins

    So, some of the information is in one table, but sometimes you need information from the other table, too. You can do that with a UNION, like this:

    SELECT	-id
    FROM	table1
    WHERE	id = &id_wanted
      UNION ALL
    SELECT  -required_table1_id
    FROM	required_table2
    WHERE	table1_id = &id_wanted
    ;
    

    The query above uses a substitution variable to pass in the id_wanted value. You could use a WHERE clause, or a table, or a bind variable, or any other technique instead.

Answers

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

    Hi, @Gary Perkins

    So, some of the information is in one table, but sometimes you need information from the other table, too. You can do that with a UNION, like this:

    SELECT	-id
    FROM	table1
    WHERE	id = &id_wanted
      UNION ALL
    SELECT  -required_table1_id
    FROM	required_table2
    WHERE	table1_id = &id_wanted
    ;
    

    The query above uses a substitution variable to pass in the id_wanted value. You could use a WHERE clause, or a table, or a bind variable, or any other technique instead.

  • Gary Perkins
    Gary Perkins Member Posts: 24 Red Ribbon

    Thanks, I knew it would be simple

    Cheers

    Gary

  • Gary Perkins
    Gary Perkins Member Posts: 24 Red Ribbon

    Actually I may have over simplified my example.

    This table is a list of names that can have one or more authorisation id's

    If we need to return all id's for a name I dont know how to put the where clause into thre UNION table?


    CREATE table "TEST_AUTH" (

      "NAME"    VARCHAR2(20),

      "ID"     NUMBER

    )

    /

    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")

    /

  • Gary Perkins
    Gary Perkins Member Posts: 24 Red Ribbon

    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;

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

    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.

  • Gary Perkins
    Gary Perkins Member Posts: 24 Red Ribbon

    Thanks Frank, really appreciate your help.

    I'll be query for one name only and returning all the id's for that name


    So pass in Joe

    result set:

    2

    3

    5

This discussion has been closed.