Question: I have been trying to develop a query to solve a problem but its been hard.

Table 1:

```
+------+----+
| NAME | ID |
+------+----+
| A    |  1 |
| A    |  2 |
| B    |  1 |
| B    |  5 |
| C    |  8 |
+------+----+

```

Table 2:

```
+------+----+
| NAME | ID |
+------+----+
| A    |  1 |
| A    |  4 |
| B    |  3 |
| B    |  5 |
| D    |  9 |
+------+----+

```

From these results, I need to return everything from table 2 that the name contains in table 1 and the ID don't.

So, this example, the return should be:

```
+------+----+
| NAME | ID |
+------+----+
| A    |  4 |
| B    |  3 |
+------+----+

```

Answer: 
You might wanna try this:

EDIT: replaced table1 and table2 with a simple subqueries in WITH clause.

```
WITH table1 AS
(
SELECT
  DECODE(LEVEL,1, 'A',2, 'A',3, 'B',4, 'B',5, 'C') AS name
  ,DECODE(LEVEL,1, 1,2, 2,3, 1,4, 5,5, 8) AS id
FROM
  dual
CONNECT BY LEVEL < 6
)
,table2 AS
(
SELECT
  DECODE(LEVEL,1, 'A',2, 'A',3, 'B',4, 'B',5, 'D') AS name
  ,DECODE(LEVEL,1, 1,2, 4,3, 3,4, 5,5, 9) AS id
FROM
  dual
CONNECT BY LEVEL < 6
)
SELECT
  t2.id
  ,t2.name
FROM
  table1 t1
  ,table2 t2
WHERE
  t1.name = t2.name -- here we take all the records from table2, which have the same names as in table1
MINUS -- then we "subtract" the records that have both the same name and id in both tables
SELECT
  t2.id
  ,t2.name
FROM
  table1 t1
  ,table2 t2
WHERE
  t1.name = t2.name
  AND t1.id = t2.id

```
I would do:

```
with t1 as (select 'A' name, 1 id from dual union all
            select 'A' name, 2 id from dual union all
            select 'B' name, 1 id from dual union all
            select 'B' name, 5 id from dual union all
            select 'C' name, 8 id from dual),
     t2 as (select 'A' name, 1 id from dual union all
            select 'A' name, 4 id from dual union all
            select 'B' name, 3 id from dual union all
            select 'B' name, 5 id from dual union all
            select 'D' name, 9 id from dual)
select name, id
from   t2
where  name in (select name from t1)
minus
select name, id
from   t1;

NAME         ID
---- ----------
A             4
B             3

```