Home » SQL & PL/SQL » SQL & PL/SQL » Help in SQL query (11g)
Help in SQL query [message #665939] Wed, 04 October 2017 00:01 Go to next message
dr46014
Messages: 49
Registered: February 2007
Member
I am new to SQL and need some help.
I have 3 tables.

Table A : empid,ceo_id ,hier1_id ...hier5_id
Table B : empid, full name
Table C : emp_id, email

i need to take table A as base or driver table. Join ceo_id with table B and C on id to get ceo full name and email ..similarly hier1_id to table B and C on basis of emp id to get hier1's email . Total at max 5 levels can be possible.
Re: Help in SQL query [message #665940 is a reply to message #665939] Wed, 04 October 2017 01:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
There is an example of joining three tables here,
http://www.orafaq.com/forum/mv/msg/138923/380760/#msg_380760
in that example you used an outer join, do you want one here?

And please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Re: Help in SQL query [message #665946 is a reply to message #665940] Wed, 04 October 2017 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll need need to repeat the joins to b and c for each of the columns used from a:
SELECT ..
FROM table_a a
JOIN table_b bceo ON a.ceo_id = bceo.empid
JOIN table_c cceo ON a.ceo_id = cceo.empid
JOIN table_b bhire1 ON a.hier1_id = bhire1.empid
JOIN table_c chire1 ON .......
........

If those hier columns can be null then you'll need outer-joins when you join them to b and c.
And b and c really should be 1 table.
Re: Help in SQL query [message #665947 is a reply to message #665946] Wed, 04 October 2017 06:04 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
You could use the oracle way of joining the tables:
WITH
    A(EMPID
     ,CEO_ID
     ,HIER1_ID
     ,HIER2_ID
     ,HIER3_ID
     ,HIER4_ID
     ,HIER5_ID)
    AS
        (SELECT 123, 123, 0, 0, 0, 0, 0 FROM DUAL
         UNION ALL
         SELECT 234, 123, 123, 0, 0, 0, 0 FROM DUAL
         UNION ALL
         SELECT 345, 123, 123, 234, 0, 0, 0 FROM DUAL
         UNION ALL
         SELECT 456, 123, 123, 234, 345, 0, 0 FROM DUAL
         UNION ALL
         SELECT 567, 123, 123, 234, 345, 456, 0 FROM DUAL
         UNION ALL
         SELECT 678, 123, 123, 234, 345, 456, 567 FROM DUAL),
    B(EMPID, FULL_NAME)
    AS
        (SELECT 123, 'queen' FROM DUAL
         UNION ALL
         SELECT 234, 'worker ant 1' FROM DUAL
         UNION ALL
         SELECT 345, 'worker ant 2' FROM DUAL
         UNION ALL
         SELECT 456, 'worker ant 3' FROM DUAL
         UNION ALL
         SELECT 567, 'worker ant 4' FROM DUAL
         UNION ALL
         SELECT 678, 'worker ant 5' FROM DUAL),
    C(EMP_ID, EMAIL)
    AS
        (SELECT 123, 'queen@anthill.net' FROM DUAL
         UNION ALL
         SELECT 234, 'worker_ant_1@anthill.net' FROM DUAL
         UNION ALL
         SELECT 345, 'worker_ant_2@anthill.net' FROM DUAL
         UNION ALL
         SELECT 456, 'worker_ant_3@anthill.net' FROM DUAL
         UNION ALL
         SELECT 567, 'worker_ant_4@anthill.net' FROM DUAL
         UNION ALL
         SELECT 678, 'worker_ant_5@anthill.net' FROM DUAL)
SELECT A.EMPID, B.FULL_NAME, C.EMAIL
       ,A.CEO_ID, B_CEO.FULL_NAME AS CEO_FULL_NAME, C_CEO.EMAIL AS CEO_EMAIL
       ,A.HIER1_ID, B_HIER1.FULL_NAME AS HIER1_FULL_NAME, C_HIER1.EMAIL AS HIER1_EMAIL
       ,A.HIER2_ID, B_HIER2.FULL_NAME AS HIER2_FULL_NAME, C_HIER2.EMAIL AS HIER2_EMAIL
       ,A.HIER3_ID, B_HIER3.FULL_NAME AS HIER3_FULL_NAME, C_HIER3.EMAIL AS HIER3_EMAIL
       ,A.HIER4_ID, B_HIER4.FULL_NAME AS HIER4_FULL_NAME, C_HIER4.EMAIL AS HIER4_EMAIL
       ,A.HIER5_ID, B_HIER5.FULL_NAME AS HIER5_FULL_NAME, C_HIER5.EMAIL AS HIER5_EMAIL
  FROM A,B,C
      ,B B_CEO,C C_CEO
      ,B B_HIER1,C C_HIER1
      ,B B_HIER2,C C_HIER2
      ,B B_HIER3,C C_HIER3
      ,B B_HIER4,C C_HIER4
      ,B B_HIER5,C C_HIER5
 WHERE A.EMPID = B.EMPID 
   AND A.EMPID = C.EMP_ID 
   AND A.CEO_ID = B_CEO.EMPID 
   AND A.CEO_ID = C_CEO.EMP_ID
   AND A.HIER1_ID = B_HIER1.EMPID(+) 
   AND A.HIER1_ID = C_HIER1.EMP_ID(+)
   AND A.HIER2_ID = B_HIER2.EMPID(+) 
   AND A.HIER2_ID = C_HIER2.EMP_ID(+)
   AND A.HIER3_ID = B_HIER3.EMPID(+) 
   AND A.HIER3_ID = C_HIER3.EMP_ID(+)
   AND A.HIER4_ID = B_HIER4.EMPID(+) 
   AND A.HIER4_ID = C_HIER4.EMP_ID(+)
   AND A.HIER5_ID = B_HIER5.EMPID(+) 
   AND A.HIER5_ID = C_HIER5.EMP_ID(+)
ORDER BY A.EMPID

or the ANSI-92 way which is preferable because it separates the relationship logic from the filter logic (the WHERE part of the SQL):
WITH
    A(EMPID
     ,CEO_ID
     ,HIER1_ID
     ,HIER2_ID
     ,HIER3_ID
     ,HIER4_ID
     ,HIER5_ID)
    AS
        (SELECT 123, 123, 0, 0, 0, 0, 0 FROM DUAL
         UNION ALL
         SELECT 234, 123, 123, 0, 0, 0, 0 FROM DUAL
         UNION ALL
         SELECT 345, 123, 123, 234, 0, 0, 0 FROM DUAL
         UNION ALL
         SELECT 456, 123, 123, 234, 345, 0, 0 FROM DUAL
         UNION ALL
         SELECT 567, 123, 123, 234, 345, 456, 0 FROM DUAL
         UNION ALL
         SELECT 678, 123, 123, 234, 345, 456, 567 FROM DUAL),
    B(EMPID, FULL_NAME)
    AS
        (SELECT 123, 'queen' FROM DUAL
         UNION ALL
         SELECT 234, 'worker ant 1' FROM DUAL
         UNION ALL
         SELECT 345, 'worker ant 2' FROM DUAL
         UNION ALL
         SELECT 456, 'worker ant 3' FROM DUAL
         UNION ALL
         SELECT 567, 'worker ant 4' FROM DUAL
         UNION ALL
         SELECT 678, 'worker ant 5' FROM DUAL),
    C(EMP_ID, EMAIL)
    AS
        (SELECT 123, 'queen@anthill.net' FROM DUAL
         UNION ALL
         SELECT 234, 'worker_ant_1@anthill.net' FROM DUAL
         UNION ALL
         SELECT 345, 'worker_ant_2@anthill.net' FROM DUAL
         UNION ALL
         SELECT 456, 'worker_ant_3@anthill.net' FROM DUAL
         UNION ALL
         SELECT 567, 'worker_ant_4@anthill.net' FROM DUAL
         UNION ALL
         SELECT 678, 'worker_ant_5@anthill.net' FROM DUAL)
SELECT   A.EMPID
        ,B.FULL_NAME
        ,C.EMAIL
        ,A.CEO_ID
        ,B_CEO.FULL_NAME AS CEO_FULL_NAME
        ,C_CEO.EMAIL     AS CEO_EMAIL
        ,A.HIER1_ID
        ,B_HIER1.FULL_NAME AS HIER1_FULL_NAME
        ,C_HIER1.EMAIL   AS HIER1_EMAIL
        ,A.HIER2_ID
        ,B_HIER2.FULL_NAME AS HIER2_FULL_NAME
        ,C_HIER2.EMAIL   AS HIER2_EMAIL
        ,A.HIER3_ID
        ,B_HIER3.FULL_NAME AS HIER3_FULL_NAME
        ,C_HIER3.EMAIL   AS HIER3_EMAIL
        ,A.HIER4_ID
        ,B_HIER4.FULL_NAME AS HIER4_FULL_NAME
        ,C_HIER4.EMAIL   AS HIER4_EMAIL
        ,A.HIER5_ID
        ,B_HIER5.FULL_NAME AS HIER5_FULL_NAME
        ,C_HIER5.EMAIL   AS HIER5_EMAIL
    FROM A
         JOIN B ON (A.EMPID = B.EMPID)
         JOIN C ON (A.EMPID = C.EMP_ID)
         LEFT JOIN B B_CEO ON (A.CEO_ID = B_CEO.EMPID)
         LEFT JOIN C C_CEO ON (A.CEO_ID = C_CEO.EMP_ID)
         LEFT JOIN B B_HIER1 ON (A.HIER1_ID = B_HIER1.EMPID)
         LEFT JOIN C C_HIER1 ON (A.HIER1_ID = C_HIER1.EMP_ID)
         LEFT JOIN B B_HIER2 ON (A.HIER2_ID = B_HIER2.EMPID)
         LEFT JOIN C C_HIER2 ON (A.HIER2_ID = C_HIER2.EMP_ID)
         LEFT JOIN B B_HIER3 ON (A.HIER3_ID = B_HIER3.EMPID)
         LEFT JOIN C C_HIER3 ON (A.HIER3_ID = C_HIER3.EMP_ID)
         LEFT JOIN B B_HIER4 ON (A.HIER4_ID = B_HIER4.EMPID)
         LEFT JOIN C C_HIER4 ON (A.HIER4_ID = C_HIER4.EMP_ID)
         LEFT JOIN B B_HIER5 ON (A.HIER5_ID = B_HIER5.EMPID)
         LEFT JOIN C C_HIER5 ON (A.HIER5_ID = C_HIER5.EMP_ID)
ORDER BY A.EMPID

Finally a short hint. Usual hierarchies are represented by connecting a value only to its direct predecessor and let your database do the rest. Here is a short glimpse of what might await you if you go down that rabbit hole:
WITH
    A(EMPID, SUPERVISORID)
    AS
        (SELECT 123, 0 FROM DUAL
         UNION ALL
         SELECT 234, 123 FROM DUAL
         UNION ALL
         SELECT 345, 234 FROM DUAL
         UNION ALL
         SELECT 456, 345 FROM DUAL
         UNION ALL
         SELECT 567, 456 FROM DUAL
         UNION ALL
         SELECT 678, 567 FROM DUAL),
    B(EMPID, FULL_NAME)
    AS
        (SELECT 123, 'queen' FROM DUAL
         UNION ALL
         SELECT 234, 'worker ant 1' FROM DUAL
         UNION ALL
         SELECT 345, 'worker ant 2' FROM DUAL
         UNION ALL
         SELECT 456, 'worker ant 3' FROM DUAL
         UNION ALL
         SELECT 567, 'worker ant 4' FROM DUAL
         UNION ALL
         SELECT 678, 'worker ant 5' FROM DUAL),
    C(EMP_ID, EMAIL)
    AS
        (SELECT 123, 'queen@anthill.net' FROM DUAL
         UNION ALL
         SELECT 234, 'worker_ant_1@anthill.net' FROM DUAL
         UNION ALL
         SELECT 345, 'worker_ant_2@anthill.net' FROM DUAL
         UNION ALL
         SELECT 456, 'worker_ant_3@anthill.net' FROM DUAL
         UNION ALL
         SELECT 567, 'worker_ant_4@anthill.net' FROM DUAL
         UNION ALL
         SELECT 678, 'worker_ant_5@anthill.net' FROM DUAL),
    T1(EMPID
      ,LVL
      ,ROOT_ID
      ,SUPERVISOR_ID
      ,FULL_PATH
      ,FULL_NAME
      ,EMAIL)
    AS
        (SELECT A.EMPID
               ,1                                    AS LVL
               ,A.EMPID                              AS ROOT_ID
               ,NULL                                 AS SUPERVISOR_ID
               ,B.FULL_NAME || '(' || C.EMAIL || ')' AS FULL_PATH
               ,B.FULL_NAME
               ,C.EMAIL
           FROM A
                JOIN B ON (A.EMPID = B.EMPID)
                JOIN C ON (A.EMPID = C.EMP_ID)
          WHERE A.EMPID = 123
         UNION ALL
         -- Recursive member.
         SELECT T2.EMPID
               ,LVL + 1                                                           AS LVL
               ,ROOT_ID
               ,ROOT.EMPID                                                        AS SUPERVISOR_ID
               ,ROOT.FULL_PATH || ' --> ' || B.FULL_NAME || '(' || C.EMAIL || ')' AS FULL_PATH
               ,B.FULL_NAME
               ,C.EMAIL
           FROM A   T2
                JOIN B ON (T2.EMPID = B.EMPID)
                JOIN C ON (T2.EMPID = C.EMP_ID)
                LEFT OUTER JOIN B B_SUPERVISOR ON (T2.EMPID = B_SUPERVISOR.EMPID)
                LEFT OUTER JOIN C C_SUPERVISOR ON (T2.EMPID = C_SUPERVISOR.EMP_ID),
                T1  ROOT
          WHERE ROOT.EMPID = T2.SUPERVISORID)
SELECT EMPID
      ,FULL_NAME
      ,EMAIL
      ,FULL_PATH
  FROM T1
Re: Help in SQL query [message #665949 is a reply to message #665939] Wed, 04 October 2017 06:30 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
dr46014 wrote on Wed, 04 October 2017 00:01
I am new to SQL and need some help.
I have 3 tables.

Table A : empid,ceo_id ,hier1_id ...hier5_id
Table B : empid, full name
Table C : emp_id, email

i need to take table A as base or driver table. Join ceo_id with table B and C on id to get ceo full name and email ..similarly hier1_id to table B and C on basis of emp id to get hier1's email . Total at max 5 levels can be possible.
Where did this table "design" come from? It appears to break every fundamental design rule. In addition to Cookiemonster's comment that Tables B and C should be one table, you have repeating elements in table A (hier1_id ...hier5_id) that should be individual rows in a child table. Google "data normalization" and "third normal form". These are fundamental design principles that go to the very heart of relational databases. Tables should be designed based on the relationships of the various data elements, not by trying to second guess how a particular query might be resolved.
Re: Help in SQL query [message #665950 is a reply to message #665939] Wed, 04 October 2017 06:54 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
dr46014 wrote on Wed, 04 October 2017 00:01
I am new to SQL and need some help.
I have 3 tables.

Table A : empid,ceo_id ,hier1_id ...hier5_id
Table B : empid, full name
Table C : emp_id, email

i need to take table A as base or driver table. Join ceo_id with table B and C on id to get ceo full name and email ..similarly hier1_id to table B and C on basis of emp id to get hier1's email . Total at max 5 levels can be possible.
Also posted on the OTN forums - https://community.oracle.com/thread/4086326
Previous Topic: how to run SQL Developer in debug
Next Topic: Sending Single email from database in case of multiple updates
Goto Forum:
  


Current Time: Thu Mar 28 08:07:27 CDT 2024