Combining column values from different tables

Question

I stuck. I have 2 tables - look at image no.1 Table columns And i would like to build query, that will give me the result - it is showed on image no 2. the result of query.

I have 2 queries and I would like to mix them up, to obtain the list from image no.2. Please help me, how to build a query.

Query no1: SELECT department_name, department_id FROM DEPARTMENTS WHERE department_id between 90 AND 110;
Query no 2: SELECT last_name, department_id from employees WHERE department_id between 90 AND 110;

score 1 · Accepted Answer · answered May 07 '16 at 22:08

Query:

with departments (department_id, department_name) as (
        select  90, 'Executive'  from dual union all
        select 100, 'Finance'    from dual union all
        select 110, 'Accounting' from dual
     ),
     employees (employee_id, last_name, department_id) as (
        select 1003, 'King'     ,  90 from dual union all
        select 1005, 'De Hann'  ,  90 from dual union all
        select 1009, 'Gietz'    , 110 from dual union all
        select 1013, 'Popp'     , 100 from dual union all
        select 1014, 'Chen'     , 100 from dual union all
        select 1015, 'Higgins'  , 110 from dual union all
        select 1029, 'Greenberg', 100 from dual union all
        select 1040, 'Kochar'   ,  90 from dual union all
        select 1043, 'Faviet'   , 100 from dual union all
        select 1045, 'Urman'    , 100 from dual union all
        select 1049, 'Sciarra'  , 100 from dual
     )
     --   end input data;   begin actual query   --
select c_name, department_id from 
  ( select department_name as c_name, department_id, 0 as categ from departments
    union all
    select '    ' || last_name as c_name, department_id, 1 from employees
    order by department_id, categ, c_name
  );

Result:

C_NAME        DEPARTMENT_ID
------------- -------------
Executive                90
    De Hann              90
    King                 90
    Kochar               90
Finance                 100
    Chen                100
    Faviet              100
    Greenberg           100
    Popp                100
    Sciarra             100
    Urman               100
Accounting              110
    Gietz               110
    Higgins             110

You don't need the "with ..." part; just use the query that begins at the SELECT statement after the two factored subqueries (after the "input data"). I even ordered by last name within each department for you; if that is not needed, just delete "c_name" from the ORDER BY clause.

I called the first column c_name; you may call it whatever you want, but calling it department_name when it also holds employee last names didn't make much sense to me. To call it whatever you want, change the SELECT statement from SELECT c_name, department_id to SELECT c_name AS whatever, department_id...

Great answer. I never used UNION, possibly the reason I wasn't aware of the usage. Thanks for a better answer. — Pirate X, May 07 '16 at 22:26
Thank you mathguy for you help. This answer is this what i asked. — Radi Em, May 07 '16 at 22:27

Pirate X · Answer 2 · 2016-05-07T21:18:57.150

0

SELECT c.last_name,
       d.department_id,
       d.department_name
FROM employee c
JOIN deptartment d ON d.department_id=c.department_id
WHERE d.department_id BETWEEN 90 AN 110

OUTPUT from my sample table

+-------+----+------------+
| KING  | 10 | ACCOUNTING |
| BLAKE | 30 | SALES      |
| CLARK | 10 | ACCOUNTING |
| JONES | 20 | RESEARCH   |
| SCOTT | 20 | RESEARCH   |
+-------+----+------------+

edited May 07 '16 at 21:18

answered May 07 '16 at 21:04

Pirate X

3,023
5
33
60

Thanks, is there any posibility do list in a different way ? DEPARTMENT_NAME ------DEPARTMENT_ID ____________________________________________ And below LAST_NAME list – Radi Em May 07 '16 at 21:25
Differently, how ? – Pirate X May 07 '16 at 21:26
@PirateX - of course you can, you just need to know how to do it. I will post the solution shortly. – May 07 '16 at 22:01

Combining column values from different tables

2 Answers2