Home » SQL & PL/SQL » SQL & PL/SQL » Oracle JOIN tables (Oracle 19.2)
Oracle JOIN tables [message #685872] Mon, 11 April 2022 12:34 Go to next message
Unclefool
Messages: 47
Registered: August 2021
Member
I'm struggling to include the computer table information with the employees table.

I successfully left joined each employee with thier manager but also want to include the computer information with each row. As you can see the common column is serial_number.


CREATE TABLE computers (serial_number,  manufacturer, model) AS
SELECT 'D123',  'Dell', 'laptop' FROM DUAL UNION ALL
SELECT 'D124',  'Dell', 'laptop' FROM DUAL UNION ALL
SELECT 'A1424',  'Apple', 'laptop' FROM DUAL UNION ALL
SELECT 'A1425',  'Apple', 'laptop' FROM DUAL UNION ALL
SELECT 'C1725',  'compaq', 'tower' FROM DUAL UNION ALL
SELECT 'C1726',  'compaq', 'tower' FROM DUAL UNION ALL
SELECT 'C1727',  'compaq', 'tower' FROM DUAL;


CREATE TABLE employees (employee_id, manager_id, first_name, last_name, serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron','D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chase','A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Doris','A1425' FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', 'Evans','C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Frank','C1726' FROM DUAL UNION ALL
SELECT 7, 6, 'Gemma', 'Grace','C1727' FROM DUAL;

select 
EMP.EMPLOYEE_ID,
EMP.FIRST_NAME,    EMP.LAST_NAME,
EMP.MANAGER_ID,
M.FIRST_NAME,    M.LAST_NAME
  from 
 employees emp
  LEFT OUTER JOIN employees m ON
emp.MANAGER_ID
 = m.EMPLOYEE_ID
ORDER BY EMP.EMPLOYEE_ID;

Re: Oracle JOIN tables [message #685873 is a reply to message #685872] Mon, 11 April 2022 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I fail to see where is your problem; just join with the computers table on serial_number.

Re: Oracle JOIN tables [message #685874 is a reply to message #685873] Mon, 11 April 2022 13:33 Go to previous messageGo to next message
Unclefool
Messages: 47
Registered: August 2021
Member
I can't seem to get the syntax correct.
select
EMP.EMPLOYEE_ID,
EMP.FIRST_NAME, EMP.LAST_NAME,
EMP.MANAGER_ID,
M.FIRST_NAME, M.LAST_NAME,
EMP.serial_number,
C.manufacturer,
C.model
from
employees emp
computers c
LEFT OUTER JOIN employees m ON
emp.MANAGER_ID
= m.EMPLOYEE_ID AND
emp.serial_number = c.serial_number
ORDER BY EMP.EMPLOYEE_ID;


Re: Oracle JOIN tables [message #685875 is a reply to message #685874] Mon, 11 April 2022 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select
  2  EMP.EMPLOYEE_ID,
  3  EMP.FIRST_NAME,    EMP.LAST_NAME,
  4  EMP.MANAGER_ID,
  5  M.FIRST_NAME,    M.LAST_NAME,
  6  EMP.serial_number,
  7  C.manufacturer,
  8  C.model
  9    from
 10   employees emp
 11   JOIN computers c ON emp.serial_number = c.serial_number
 12   LEFT OUTER JOIN employees m ON emp.MANAGER_ID = m.EMPLOYEE_ID
 13  ORDER BY EMP.EMPLOYEE_ID;
EMPLOYEE_ID FIRST LAST_ MANAGER_ID FIRST LAST_ SERIA MANUFA MODEL
----------- ----- ----- ---------- ----- ----- ----- ------ ------
          1 Alice Abbot                        D123  Dell   laptop
          2 Beryl Baron          1 Alice Abbot D124  Dell   laptop
          3 Carol Chase          1 Alice Abbot A1424 Apple  laptop
          4 Debra Doris          2 Beryl Baron A1425 Apple  laptop
          5 Emily Evans          3 Carol Chase C1725 compaq tower
          6 Fiona Frank          3 Carol Chase C1726 compaq tower
          7 Gemma Grace          6 Fiona Frank C1727 compaq tower
Re: Oracle JOIN tables [message #685876 is a reply to message #685875] Mon, 11 April 2022 14:35 Go to previous message
Unclefool
Messages: 47
Registered: August 2021
Member
Thanks
Previous Topic: Find Difference between 2 columns along with difference data
Next Topic: Help Needed in Oracle Referenced Column DataType
Goto Forum:
  


Current Time: Tue Jun 28 16:42:43 CDT 2022