1

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?

4
  • 1
    Can you provide some sample data and desired output to illustrate your question?
    – peterm
    Sep 6, 2013 at 7:50
  • yeah .. i provided same Sep 6, 2013 at 7:57
  • And what is the desired output based on this sample data?
    – peterm
    Sep 6, 2013 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 Sep 6, 2013 at 8:11

3 Answers 3

0

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

3
  • 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... Sep 6, 2013 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, 2013 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 Sep 9, 2013 at 6:22
0

The "where" clause excludes everything in Table1

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

3
  • why where clause excludes EVERYTHING...it will exclude only PARENT PART... Sep 6, 2013 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, 2013 at 7:50
  • sir , thats why i have mentioned where child not in ( select Parent from t1) Sep 6, 2013 at 7:58
0

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

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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