inner joins with where clause for no values?

Question

I have 2 tables :

Table1

Parent(varchar) Child (varchar)

Table2

C1(varchar)    PC (varchar)

Sample data:

Requirement - I need Table2.C1 values for which column Table2.PC = Table1.Child , but Child values must be different from Table1.Parent's values..

I'm using below query in mysql:

select distinct(C1),Child,PC 
  from Table2 inner join Table1 
    on Table2.PC=Table1.Child 
 where Table1.Child not in (select Parent from Table1);

It is giving empty set, but there are values in Child which is same as in PC, but not in Parent....

Where I'm getting wrong?

Answer 1

Are you looking for this?

SELECT DISTINCT t2.c1, t2.pc, t1.child
  FROM table1 t1 LEFT JOIN table1 t3
    ON t1.child = t3.parent JOIN table2 t2
    ON t1.child = t2.PC
 WHERE t3.parent IS NULL

Output:

|   C1 |   PC | CHILD |
|------|------|-------|
| mono | qwer |  qwer |

Here is SQLFiddle demo

Answer 2

The "where" clause excludes everything in Table1

Provide sample data in the question if you would like an SQL query example

Answer 3

Runing your query with the shown data gives me 1 result with C1 = mono, Child = qwer and PC = qwer

The data you display should provide that result, according to the query.

If you do not get the same result, my guess is that you have more data in your tables than you show here which is filtering away the result; or there's some other factor you're not mentioning.