CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user6140865
user6140865

Reputation:

LEFT JOIN and finding the missing values

I'm trying to find some help with working out this function. I am trying to list all the SERIAL_NUMBER items in the oL table that do NOT exist in the rL table, that contain a particular value.

Below is the code I came up with but haven't gotten any further. No matter how I seem to write it, I always produce all of the SERIAL_NUMBER items that exist or nothing at all.

DECLARE @val int
SET @val = 43770

SELECT oL.SERIAL_NUMBER 
FROM 
    ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS AS oL 
    LEFT JOIN ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS AS rL 
        ON oL.SERIAL_NUMBER != rL.SERIAL_NUMBER
WHERE oL.SO_LINE_NUMBER = @val;

What am I doing wrong here? This code produces all of the results where the oL.SO_LINE_NUMBER = @val. I need that - minus the serial numbers that already exist in the rL table.

I've also tried adding 

WHERE rl.SERIAL_NUMBER IS NULL

but that produces nothing.

Upvotes: 1

Views: 4549

Answers (4)

Devram Kandhare
Devram Kandhare

Reputation: 781

use this: select ol.SERIAL_NUMBER from aof_order_optics ol where ol.SERIAL_NUMBER not in(select rl.SERIAL_NUMBER from aof_optic_results rl) and ol.SO_LINE_NUMBER=@val;

Upvotes: 0

Eric
Eric

Reputation: 5743

Addition on Vladirmir answer, you just mess with the joining condition and filtering condition.

However you need to take care of the multiplier effect of join if the value duplicate in rows. If you just want to check the existence, you can use EXISTS

SELECT oL.SERIAL_NUMBER 
FROM ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS oL 
WHERE 
    NOT EXISTS(
        SELECT * FROM ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS rL 
        WHERE oL.SERIAL_NUMBER = rL.SERIAL_NUMBER)
    AND oL.SO_LINE_NUMBER = @val

Upvotes: 3

alayor
alayor

Reputation: 5045

You could write a subquery like this:

DECLARE @val int;
SET @val = 43770;

SELECT 
    oL.SERIAL_NUMBER 
FROM 
    ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS AS oL 
WHERE 
    oL.SO_LINE_NUMBER = @val
    AND oL.SERIAL_NUMBER NOT IN (
      SELECT rL.SERIAL_NUMBER
      FROM ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS AS rL
    )
;

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

Your query looks almost correct.

You should join on oL.SERIAL_NUMBER = rL.SERIAL_NUMBER (not !=) and then leave only those rows that have rL.SERIAL_NUMBER IS NULL. This filter means that there are no rows in rL with such SERIAL_NUMBER.

DECLARE @val int;
SET @val = 43770;

SELECT 
    oL.SERIAL_NUMBER 
FROM 
    ROBOTICS_OPTICS_UAT.dbo.AOF_ORDER_OPTICS AS oL 
    LEFT JOIN ROBOTICS_OPTICS_UAT.dbo.AOF_OPTIC_RESULTS AS rL
        ON oL.SERIAL_NUMBER = rL.SERIAL_NUMBER
WHERE 
    oL.SO_LINE_NUMBER = @val
    AND rL.SERIAL_NUMBER IS NULL
;

Upvotes: 1

Related Questions