Problem of WHERE clause in SQL statement

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.

Code:

---

select name, job
from table1
where (name | | job) in (
select (name | | job)
from tabell2);

---

or

Code:

---

select name,job
from table1
where (name,job)exist in
select (name, job)
from tabel2;

---

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.