Search code examples
sqlselect

How to exclude lines where a data is being referenced in another column SQL

I need help with a problem to exclude matching rows.

I have a table that has multiple columns,this table refers to a travel, the rows can contain data to a full travel, the first part of the travel or the second part of the travel.

Table example 1

SC_ID REF SOL_ID
100.500 350
100.501 AAAA 390
100.502 350 500
100.503 777

In this table, "SC_ID" and "SOL_ID" are indepentent numbers and "REF" might containt the reference value of the "SOL_ID" of another line (to link the first part of a travel with the second). This column, "REF", can be null, can be whatever or can be the "SOL_ID" number.

What I want is a select statement for atable where all the lines that have a reference "REF to another "SOL_ID" being excluded, together with all lines that are referenced by their "SOL_ID" in a "REF".

Based on the example table: SC_ID 100.500 and 100.502 should be excluded of the select view because they are referenced. And the result should be only: 2

SC_ID REF SOL_ID
100.501 AAAA 390
100.503 777

To summary what I want to not show: If the "REF" is null but the "SOL_ID" of that line is in any other "REF" in the table, this line should be excluded from the select. If the "REF" is present, and it is a "SOL_ID" in the table, this line should be excluded from the select.

To summary what I want to show: All data with null "REF" that have their "SOL_ID" not present in the "REF" column of another data. All data with "REF" values that are not a "SOL_ID" in the table.

I've tried doing self joins, left join... But the problem is that the second part of the travel keep showing (row with REF with SOL_ID of the previous travel) or none value at all. Or maybe it's just a mess because it's one table for all...

SELECT * 
FROM travel_history th1 
JOIN travel_history th2 on th1."SOL_ID" = th2."SOL_ID"
WHERE
(th1."REF" is null and th1."SOL_ID" not in th2."REF")
and
th1."REF" not in th2."SOL_ID";
Solution

  • This correction should produce the results you want

    SELECT * 
FROM travel_history th1 
--INNER JOIN travel_history th2 on th1.SOL_ID = th2.SOL_ID
WHERE
  th1.SOL_ID not in (SELECT REF from travel_history)
  AND
  th1.REF not in  (SELECT SOL_ID from travel_history)

    OR

    --Simplified self join
SELECT th1.* 
FROM travel_history th1 
LEFT JOIN travel_history th2 on th1.SOL_ID = th2.REF OR th1.REF = th2.SOL_ID
WHERE
  th2.SC_ID is null

    SQL-Server fiddle example

    SC_ID REF SOL_ID
    100.501 AAAA 390
    100.503 777