Home » SQL & PL/SQL » SQL & PL/SQL » Help in SQL query (11g)
Help in SQL query [message #665939] |
Wed, 04 October 2017 00:01 |
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 #665946 is a reply to message #665940] |
Wed, 04 October 2017 05:38 |
cookiemonster
Messages: 13943 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 |
|
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 #665950 is a reply to message #665939] |
Wed, 04 October 2017 06:54 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
dr46014 wrote on Wed, 04 October 2017 00:01I 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 07:59:47 CDT 2024
|