Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LEFT JOIN to display records not in Right table

I have two tables ECRDTL_del and ECRDTL_edit, I want to display only records from ECRDTL_del which are not in ECRDTL_edit. I have tried using MYSQL left join, but unable to get the desired result. Here is my current querystring:

SELECT a.*
FROM ECRDTL_del AS a
    LEFT JOIN ECRDTL_edit AS b ON b.Ecrno = a.Ecrno
WHERE b.Cylno <> a.Cylno

Data Example of both the tables and expected result out of these two mentioned below:

ECRDTL_del:

Ecrno  Cylno
9090   8881
9090   8882
9090   8883
9090   8884

ECRDTL_edit:

Ecrno  Cylno
9090   8881
9090   8885
9090   8886
9090   8884

Result expected after LEFT join:

Ecrno  Cylno
9090   8882
9090   8883
like image 939
sridhar s Avatar asked Dec 04 '22 11:12

sridhar s


1 Answers

From your question:

"...I want to display only records from ECRDTL_del which are not in ECRDTL_edit."

You are close, you just need to check if b.Ecrno IS NULL.

SELECT a.* 
FROM   ECRDTL_del AS a 
       LEFT JOIN ECRDTL_edit AS b 
          ON a.Ecrno = b.Ecrno AND 
             a.Cylno = b.Cylno
WHERE  b.Ecrno IS NULL
  • SQLFiddle Demo

OUTPUT

╔═══════╦═══════╗
║ ECRNO ║ CYLNO ║
╠═══════╬═══════╣
║  9090 ║  8882 ║
║  9090 ║  8883 ║
╚═══════╩═══════╝
like image 130
John Woo Avatar answered Dec 08 '22 04:12

John Woo