LEFT JOIN to display records not in Right table

left-join, mysql, sql

Solution

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 ║
╚═══════╩═══════╝

Problem

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 ```

Original source