SQL Query SELECT same data from different tables, show all records, but show / display matches

I have two tables where I would like to compare information (In order to get from the initial table to the other I need to go through a reference table). I am getting the results I am looking for except when a match is found an extra row of data is added (screen shot below). There should be only 4 rows, I don’t understand why the value in column 1 row 5 wasn’t just added to column 1 row 4.

Any help would be much appreciated.

Code

Select DISTINCT
 CASE
    WHEN LIC.ORDER_NUM = LN_STLIC.ORDER_NUMBER THEN LIC.ORDER_NUM 
    ELSE ''
END 'ORDER Number 1',
LN_STLIC.ORDER_NUMBER 'ORDER Number 2'
from LN_TABLE1 LN_STLIC 
    LEFT OUTER JOIN LN_REF LN_PDE_RTN on LN_STLIC.LNPID = LN_PDE_RTN.LNPID
    LEFT OUTER JOIN LN_TABLE2 LIC on LN_PDE_RTN.ID = LIC.ID 
where LIC.ID = '123456'

Example Table Data

LN_TABLE1

LN_REF

LN_TABLE2

Results

Answer

You’ve defined Order Number 1 as

CASE
    WHEN LIC.ORDER_NUM = LN_STLIC.ORDER_NUMBER THEN LIC.ORDER_NUM 
    ELSE ''
END 

So, you can reasonably infer that when Order Number 1 is blank, it’s because LIC.ORDER_NUM doesn’t match LN_STLIC.ORDER_NUMBER.

You asked for a DISTINCT on the combination of Order Number 1 and Order Number 2. So every combination of the two of them that appears in the data, will appear just once.

Because LN_TABLE1 has four different order numbers for the same value of LNPID, you’re going to generate 3 records with a blank Order Number 1 and Order Number 2 set to 210414. But the distinct will take it down to just one, plus the one where they match (records 4 and 5 in your example).

You’d probably have to join on LIC.ORDER_NUM = LN_STLIC.ORDER_NUMBER in order to match the order numbers to each other, and get only 1 record for order number 210414.

I could give you a better query if I knew a bit more about what you were trying to achieve.