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";
L Z B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
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 |
1
Thanks for the help!
The first version did not work, I believe because I have to do castings and the ” th1.REF not in” line came back empty.
The simplified version did the trick.
I just got to do some casting since SOL_ID is bigint and REF is a varchar, I also had to change the SC_ID to SOL_ID.
–Code that I used
SELECT th1.*
FROM travel_history th1
LEFT JOIN travel_history th2 on cast(th1.SOL_ID as varchar) = th2.REF
OR th1.REF = cast(th2.SOL_ID as varchar)
WHERE
th2.SOL_ID is null
L Z B is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.