CODEWITHSUNDEEP
sqloracle-database
user3026370
user3026370

Reputation: 11

Join to subquery

q1:

 SELECT SUBSTR(o.first_name,1,1)||' '||o.last_name "NAME",
    FROM employees o
    WHERE o.salary >
    (SELECT AVG(i.salary)
    FROM employees i
    WHERE i.department_id =
    o.department_id)

I have a department table with department_id and department_name || how to join it to this result to display the results of the subquery and dep name ?

q2)this throws an error , after adding the last line : why?

SELECT SUBSTR(first_name, 1, 1) || ' ' || last_name "Employee Name", department_id "Department Id", to_char(NULL) "Department Name", to_char(NULL) " City" 
FROM employees
UNION
SELECT to_char(NULL) "Employee Name" , department_id "Department ID", department_name "Department Name", to_char(NULL)" City" 
FROM departments
UNION 
SELECT to_char(NULL) "Employee Name" , to_char(NULL) "Department Id", to_char(NULL) "Department Name" ,to_char(NULL )"City"
FROM locations

Upvotes: 1

Views: 126

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

For your first query try:

SELECT SUBSTR(o.first_name, 1, 1) || ' ' || o.last_name "NAME",
  d.department_name "DEP NAME"
FROM employees o
INNER JOIN department d ON d.department_id = o.department_id
WHERE o.salary > (
    SELECT AVG(i.salary)
    FROM employees i
    WHERE i.department_id = o.department_id
    )

Your error comes most likely from having to_char(null) for department_id when this column isn't a CHAR.

Just use null instead:

SELECT SUBSTR(first_name, 1, 1) || ' ' || last_name "Employee Name",
  department_id "Department Id",
  to_char(NULL) "Department Name",
  to_char(NULL) " City"
FROM employees    
UNION    
SELECT to_char(NULL) "Employee Name",
  department_id "Department ID",
  department_name "Department Name",
  to_char(NULL) " City"
FROM departments    
UNION    
SELECT to_char(NULL) "Employee Name",
  NULL "Department Id",     -- Replace to_char(null) with NULL
  to_char(NULL) "Department Name",
  city_name "City"    -- Add city_name column to get results different than NULL
FROM locations

Upvotes: 1

Related Questions