Get unjoined values in mysql

Question

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.

possible duplicate of MySQL "NOT IN" query

Barmar
– Barmar

2014-03-18 07:33:30 +00:00
Commented Mar 18, 2014 at 7:33 — Barmar
– Barmar, Commented Mar 18, 2014 at 7:33

NullReferenceException · Accepted Answer · 2014-03-18 07:36:32Z

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

answered Mar 18, 2014 at 7:36

NullReferenceException

1,64915 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sai Avinash · Accepted Answer · 2014-03-18 07:31:04Z

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

answered Mar 18, 2014 at 7:31

Sai Avinash

4,75117 gold badges63 silver badges100 bronze badges

Comments

Jim Garrison · Accepted Answer · 2014-03-18 07:31:17Z

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.

answered Mar 18, 2014 at 7:31

Jim Garrison

87k20 gold badges161 silver badges196 bronze badges

Comments

Rohit · Accepted Answer · 2014-03-18 08:14:39Z

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

answered Mar 18, 2014 at 8:14

Rohit

3342 silver badges9 bronze badges

Comments

António Almeida · Accepted Answer · 2014-03-18 09:41:13Z

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

edited Mar 18, 2014 at 9:41

António Almeida

10.2k8 gold badges62 silver badges72 bronze badges

answered Mar 18, 2014 at 7:43

John

852 silver badges9 bronze badges

Collectives™ on Stack Overflow

Get unjoined values in mysql

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related