CODEWITHSUNDEEP
sqlsql-server
Saiyanthou
Saiyanthou

Reputation: 142

Check if a combination of fields already exists in the table

My weakest area of SQL are self JOINS, currently struggling with an issue.

I need to find the latest entry in a table, I'm using a WHERE DATEFIELD IN (SELECT MAX(DATEFIELD) FROM TABLE) to do this. I then need to establish if 3 columns from that already exist in the same TABLE.

My latest attempt looks like this - 

SELECT * FROM PART_TABLE
WHERE NOT EXISTS
(
SELECT 
t1.DATEFIELD
t1.CODE1
t1.CODE2
t1.CODE3
FROM PART_TABLE t1
INNER JOIN PART_TABLE t2 ON t1.UNIQUE = t2.UNQIUE
)
WHERE t1.DATEFIELD IN
(
SELECT MAX(DATEFIELD)
FROM PARTTABLE
)

)

I think part of the issue is that I can't exclude the unique row from t1 when checking in t2 using this method.

Using MSSQL 2014.

Upvotes: 1

Views: 2558

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

The following query will return the latest record from your table and a bit flag whether a duplicate tuple {Code1, Code2, Code3} exists in it under a different identifier:

select top (1) p.*,
  case when exists (
    select 0 from dbo.Part_Table t where t.Unique != p.Unique
    and t.Code1 = p.Code1 and t.Code2 = p.Code2 and t.Code3 = p.Code3
  ) then 1
  else 0 end as [IsDuplicateExists]
from dbo.Part_Table p
order by p.DateField desc;

You can use this example as a template to address your specific needs, which unfortunately aren't immediately apparent from your explanation.

Upvotes: 2

Related Questions