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?

  • 1
    Can you provide some sample data and desired output to illustrate your question? – peterm Sep 6 '13 at 7:50
  • yeah .. i provided same – Ankit Kapoor Sep 6 '13 at 7:57
  • And what is the desired output based on this sample data? – peterm Sep 6 '13 at 8:04
  • i need those C1 values...where Pc's value is EQUALS to child, but these EQUALS values should not be value of PARENT , ... in above attached pic , i should get C1 value where Child = 'qwer'; so qwer is not present in Parent , but is in PC and Child – Ankit Kapoor Sep 6 '13 at 8:11
up vote 0 down vote accepted

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

  • ok..its nice..but if m running below query : select distinct child from T1 where child not in ( select Parent from T1); this is giving more count ...and in your query it is is giving more...as far as i concerned this count should be same... – Ankit Kapoor Sep 6 '13 at 8:57
  • @user21546 You get more rows with your query because you have child values in table1 that are not in PC column in table2. And they are filtered out when INNER JOIN is used with join with table2. Now what I didn't get is whether the proposed query answered your question? – peterm Sep 6 '13 at 18:14
  • i have no values in Child that is not present in PC..I have every Value in PC that can or cannot be present in Parent – Ankit Kapoor Sep 9 '13 at 6:22

The "where" clause excludes everything in Table1

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

  • why where clause excludes EVERYTHING...it will exclude only PARENT PART... – Ankit Kapoor Sep 6 '13 at 7:44
  • the statement (select parent from table1) returns all parent records, so no Table1.Child cells can match any parent cells. – sa555 Sep 6 '13 at 7:50
  • sir , thats why i have mentioned where child not in ( select Parent from t1) – Ankit Kapoor Sep 6 '13 at 7:58

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.