I need help with a subquery. The problem is to create the SQL statement to find employees in Department 10 with the same positions as in Title 30
SELECT name, job
FROM "table name"
WHERE?
I need help with a subquery. The problem is to create the SQL statement to find employees in Department 10 with the same positions as in Title 30
SELECT name, job
FROM "table name"
WHERE?
Now you have not shown us any table definitions, so if I write the query does not work then the blame lies not with me. Assuming that the column job is what you call position?
Code:select name, job from table where Department=10 intersect select name, job from table where Department=30
I have a better suggestion for you.
Code:SELECT name, job FROM employees WHERE Department=10 AND position = ( SELECT position FROM employees WHERE Department=30 )
The above query will not work. You should use "=" instead of IN the subquery. And whether you use the IN then it will return the wrong result since there is some correlation between rows in the outer and inner query. One can write about to use a subquery correlated with EXISTS (), but it is much simpler and more readable to use the Intersect, I think so.
But you have not given us any indication of which database you are on, but still trying on some tips. If it is oracle database and you want to do what you have asked, then you can try one of these two methods.
orCode:select name, job from table1 where (name | | job) in ( select (name | | job) from tabell2);
Or you can of course use the Intersect as mentioned above. There are many ways to do the same on others. If it is MSSQL, then "||" should be replaced by "&" sign.Code:select name,job from table1 where (name,job)exist in select (name, job) from tabel2;
Bookmarks