1

I have 2 tables in Mysql

Table 1 --

Id     candidate_id     resume

25      100                   hjksdf.jpg

26      101                   hdd.docx

29      102                    hf.docx

30      103                   hsdfh.docx

Table 2 --

resumes_sent_id     client_id     candidate_id       date   

1                                43                100           2014-03-14 03

2                                43                101           2014-03-15 03

I need to join these two tables where client_id 43 after joining I need only "unjoined" records. If I join I can get candidate_id which values have unique 100,101. But in my output I need those data records which have 102,103 it means except unique values of joining.

Based on t2 where client_id is 43 there are two values 100 and 101 but I want to filter unmatched values like 102.

Wolfram
  • 8,044
  • 3
  • 45
  • 66
Arockiaraj
  • 353
  • 2
  • 4
  • 21
  • possible duplicate of [MySQL "NOT IN" query](http://stackoverflow.com/questions/1519272/mysql-not-in-query) – Barmar Mar 18 '14 at 07:33

5 Answers5

2

Try this

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table2.candidate_id=table1.candidate_id
WHERE table2.candidate_id IS NULL and t2.client_id=43
NullReferenceException
  • 1,641
  • 15
  • 22
1

You got to use Outer join if you want unmatched records

select your column names from table1 t1 outer join table2 t2 on t1.candidate_id=t2.candidate_id
Sai Avinash
  • 4,683
  • 17
  • 58
  • 96
0

Use a left join and test for null

select * from table1 t1
left join table2 t2 on t1.candidate_id=t2.candidate_id
where t2.candidate_id is null

left join returns all rows from the first table with t2 values where there is a match or nulls where there is no corresponding t2 row. From that result you want all rows where there was no match, resulting in t2.candidate_id being null.

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
0

Use thr following code:

SELECT * FROM `Table 1` t1 
    LEFT JOIN `Table 2` t2 
    ON t1.candidate_id=t2.candidate_id 
    WHERE t2.candidate_id IS NULL
António Almeida
  • 9,620
  • 8
  • 59
  • 66
John
  • 85
  • 2
  • 9
0

Hope this help....

IF you use not equal sign in your query then you get a result of 6 rows

SELECT     * FROM  TABLE1 T1 INNER JOIN TABLE2 T2 ON T2.CLIENT_ID= 43 AND  T1.CANDIDATE_ID  T2.CANDIDATE_ID

BUT it includes 2 rows having 100,101 due to join

....IF YOU manually wanted to remove 100,101 id from result then got for this query..

SELECT * FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T2.CLIENT_ID= 43 AND T1.CANDIDATE_ID  T2.CANDIDATE_ID AND T1.candidate_id NOT IN(101,100)

Thanks

Rohit
  • 324
  • 2
  • 9