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

Question

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

Lukasz Szozda · Accepted Answer · 2019-07-27 18:21:29Z

1

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

edited Jul 27, 2019 at 18:21

answered Jul 27, 2019 at 17:30

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

7 Comments

Tom Over a year ago

thanks for helping me but I am getting values for roll no 1001 : 3, 4, 5, how can I get 1, 3, 4, 5?

Lukasz Szozda Over a year ago

@Tom WHERE b.Ans_Option_No IS NULL1

Tom Over a year ago

okay how can I add" b.Question_No is NULL " along with above code which u had written earlier ?

Tom Over a year ago

hi I am not getting questions which Ans_No contain NULL values, I am getting questions 3, 4 , 5 but not 1, 3 , 4, 5

Lukasz Szozda Over a year ago

@Tom Did you run the code? dbfiddle.uk/…

|

Gordon Linoff · Accepted Answer · 2019-07-27 18:23:27Z

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.

thanks a lot for helping me out...will this query result faster or the solution provided by Lukasz ?
@Tom . . . They implement different logic. I think this is the version that you actually want.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Linked

Related