CODEWITHSUNDEEP
mysqlsqljoinwhere-clause
Tom
Tom

Reputation: 771

Fetch record from 1st table which are not in 2nd table mysql

I have following records in these two mysql tables.

Table-A

       Question_No 
           1
           2
           3
           4
           5

Table-B

Roll_No  Question_No  Ans_Option
 1001      1         NULL 
 1001      2         D
 1001      3         NULL
 1002      1         C
 1002      2         NULL

Here the word "NULL" is explicitly inserted into column , nothing to be confused.

How can I display the following result by mysql query?

       Questions not attempted by roll no 1001 are : 1, 3, 4, 5 
       Questions not attempted by roll no 1002 are : 2, 3, 4, 5

I tried with following code but not working 

select distinct a.* from table_A a NATURAL LEFT JOIN table_B b where 
b.Question_No IS NULL and b.Roll_No=1001;// where I am wrong here ? I have 
not set any column as primary key , should I set?

Thanks a lot in Advance

Upvotes: 1

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Use a cross join to generate the rows and then filter them out:

select r.roll_no, b.Question_No
from (select distinct roll_no from b) r cross join
     a left join
     b
     on b.roll_no = r.roll_no and b.Question_No = a.Question_No
where b.roll_no is null;

This gets the question that are not in b.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

When you put WHERE condition on LEFT OUTER JOIN it is literally the same as INNER JOIN:

select distinct a.* 
from table_A a 
NATURAL LEFT JOIN table_B b 
where b.Question_No IS NULL 
  and b.Roll_No=1001;  -- this condition makes it `NATURAL JOIN`

You could rewrite it as:

SELECT DISTINCT a.*
FROM table_A a
LEFT JOIN table_b b
  ON a.Question_No = b.Question_No
 AND b.Roll_No=1001
WHERE b.Ans_Option_No IS NULL

db<>fiddle demo

Upvotes: 1

Related Questions