Mysql – inner joins with where clause for no values

join;MySQL

I have 2 tables :

Table1

Parent(varchar) Child (varchar)

Table2

C1(varchar)    PC (varchar)

Sample data:

enter image description here

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?

Best Answer

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

Related Question