Join to subquery

Question

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

For the error, please add the error you are getting. As for your query, if you add your table structure and some sample data, maybe we can help you achieve this in a better way. As for the — Filipe Silva
– Filipe Silva, Commented Nov 24, 2013 at 2:56
@Filipe: error :ORA-01790: expression must have same datatype as corresponding expression.. the table i would like to join to into q2 has department_id, department_name.. — user3026370
– user3026370, Commented Nov 24, 2013 at 3:00
the first one i have no clue how to approach to get three columns: the name and salary (from subquery) and dep name from the other table — user3026370
– user3026370, Commented Nov 24, 2013 at 4:24

Filipe Silva · Accepted Answer · 2013-11-24 03:17:44Z

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

Although the error is gone, there hase to be a logical error, since as last , city names should be displayed, and there arent any in the list.--UPDATE: corrected to_char(NULL) "City" to city "city. works perfect. thanks!
@PM77-1. I'm not sure what is the reason for him to use it. I just pointed out what was most likely giving him that error.
@user3026370. there is an error for sure. You have always to_char(null) in every select for City. You might want to add the city_name(i assume) to the last query's "City" column
@user3026370. I added an attempt to answer your first problem. See if it is what you were loking for.

Collectives™ on Stack Overflow

Join to subquery

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related